SUMPRODUCT Not Working Due To Text In Column

Apr 24, 2009

I'm trying to work out how to fix the formula below to take into account and ignore and text entries, while giving me the result of the sum of column K minus the sum of column J. If I delete the text entries, the code works but I need the text entries to stay where they are. I've attached a sample sheet with fake info to explain whan I'm trying to do.

Cell N28 on the 'MGMT INFO' tab contains the following formula:

=IF(ISERROR(SUMPRODUCT((Sheet01!$K$1:$K$1000)-(Sheet01!$J$1:$J$1000))),0,(SUMPRODUCT((Sheet01!$K$1:$K$1000)-(Sheet01!$J$1:$J$1000))))

Columns J and K on the 'Sheet01' tab contain the Pay and Invoice information for all the work planners for that client that I'm trying to find the difference between. Each work planner has 'Pay' and 'Invoice' also in that column though, one entry per planner which is causing the SUMPRODUCT formula to screw up.

View 3 Replies


ADVERTISEMENT

Text To Column Not Working Within Macro?

Feb 4, 2013

I have recorded the following code, but while it works during the recording stage it doesn't within the macro.

Range("D18").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.TextToColumns Destination:=Range("D18"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
:=Array(1, 1), TrailingMinusNumbers:=True

The data within column D is imported dates and I am doing the text to columns to get a later macro to recognise the cells as dates and not text.

(The code seems to work for a few of the cells but not all of them.)

View 9 Replies View Related

Text To Column Function Not Working In VBA

May 2, 2014

I want to use TextToColumn function of excel using vba. I tried recording a macro to get the syntax and then make a few changes to it; But it is not working when i run it from vba ... if i do it manually it is working very nicely but it just skips that step in vba.

i have pasted my code

Code:
On Error Resume Next
rngReqIDcodWhole.TextToColumns Destination:=rngReqIDcodWhole, DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
Semicolon:=False, Comma:=False, Space:=False, Other:="""", FieldInfo _
:=Array(1, 2), TrailingMinusNumbers:=True
rngReqIDcodWhole is a column range

the text that the code needs to break is as follows

Code:
_BRE.RFs.DocPackage.Decpages.CreateContent_DECPAGES_RF(dre, sec, "FormID","FormID", 1, "DecR_1", "DecR_1", false);

View 7 Replies View Related

Sumproduct Is Not Working

Mar 4, 2009

=((SUMPRODUCT((('Data Sheet'!$A$2:$A$997<2110000)+('Data Sheet'!$A$2:$A$997>2119999)),--(ISNUMBER(MATCH('Data Sheet'!$B$2:$B$997,{300100,302100},0))),('Data Sheet'!E$2:E$997*IF('Data Sheet'!E1<=$F$4:$G$4,1,0))

The last part of the formula is the problem, the first two arrays are conditions and the last array is a sum if f4 a period of the year is equal or less to the data sheet tab it is summing.The formula below works fine, but when the last condition is added it doesn't work.

=((SUMPRODUCT((('Data Sheet'!$A$2:$A$997<2110000)+('Data Sheet'!$A$2:$A$997>2119999)),--(ISNUMBER(MATCH('Data Sheet'!$B$2:$B$997,{300100,302100},0))),'Data Sheet'!E$2:E$997)

View 7 Replies View Related

SUMPRODUCT Not Working For Me

Dec 8, 2006

what I want is quite simple actually. Worksheet 2 contains two columns where I want to search for certain text, when it finds the text in both columns (B & D) I want it to sum up the values in the same row on column F.

If I understood correctly there should be 2 ways to do this, using the SUMPRODUCT formula: ....

View 9 Replies View Related

Sumproduct Not Working On Blanks?

Oct 27, 2012

I am using the following formula :

=SUMPRODUCT(--('Fleet Dumps'!P6:P400+'Fleet Dumps'!K6:K400E1+E2))

This works fine as long as there is data in the ranges 6:400 when there is not in come cells it returns #VALUE, in the ranges ther is a lookup such as :

=IF(ISERROR(VLOOKUP(L6,$B$7:$C$9,2,FALSE)),"",VLOOKUP(L6,$B$7:$C$9,2,FALSE))

So not sure if that is causing it.

View 3 Replies View Related

SUMPRODUCT Not Working When Cells Blank?

Dec 30, 2013

=SUM((DATA!$F$2:$F$3000=A3)*(DATA!$N$2:$N$3000="N")*DATA!$J$2:$J$3000)

This formula doesnt work but =SUM((DATA!$F$2:$F$25=A3)* DATA!$N$2:$N$25="N")*DATA!$J$2:$J$25) works. Thats because all the other cells higher than 25 have a reference to another cell. How to solve it? Maybe with SUMPRODUCT / ISBLANK? See attached file.

View 3 Replies View Related

Formula Not Working For Sumproduct When Only Looking Up 2 Values

Jan 19, 2014

I am trying to sum up column V, when for a specific date in column z, and only sum when the two names EEM, Merri1* are involved. I used the * because there are a couple of variations for MErri1 after the 1. The formula is not working. I believe it's the way I'm combining this

[Code] .....

Full formula is below:

[Code] .....

View 14 Replies View Related

Sumproduct Cell Reference Not Working

Jun 18, 2009

I have the following line of code in the code for a textbox in a userform:

View 4 Replies View Related

Sumproduct Not Working In Closed Workbook & Shows #N/A

May 27, 2008

I am using this formula in Product Balance Update.xls,Tsuchiura worksheet that update data from A-Tsuchiura.xls, INVOICE worksheet.

When both workbooks are is opened, all linking are fine.

But upon A-Tsuchiura.xls is closed, my linking in Product Balance Update.xls,Tsuchiura worksheet will shows #N/A.

I had tried many ways for sumproduct
1) =SUMPRODUCT(--('\SomfactoryDistributionShipment[A-Tsuchiura.xls]INVOICE'!$B$14:$B$97=A3),--('\SomfactoryDistributionShipment[A-Tsuchiura.xls]INVOICE'!$G$14:$G$97))

2) =SUMPRODUCT(('\SomfactoryDistributionShipment[A-Tsuchiura.xls]INVOICE'!$B$14:$B$97=A3)*('\SomfactoryDistributionShipment[A-Tsuchiura.xls]INVOICE'!$G$14:$G$97))

I had read through this forum that sumproduct should work in closed workbook but I still cant get it why this error ocurr..

Please refer to attached to have a clearer picture of error

View 14 Replies View Related

Sumproduct Formula With Date Reference Not Working

Mar 7, 2012

I have this formula below that counts all matching cells that fall between two dates (a Monday to the next Monday) where S2 and T2 are the dates (one week apart).

I have 52 colums ie one for each week.

=SUMPRODUCT(($G$3:$G$1000>=S2)*($G$3:$G$1000

View 7 Replies View Related

SUMPRODUCT / INDIRECT Functions Not Working With ROW Reference When Combined

Apr 4, 2014

Basically I have an equation:

=SUMPRODUCT(G9:G11,H9:H11)/SUM(G9:G11)

It's just a simple percentage calculator for my purposes and works fine as is. However, I want to make it dynamic whereby from a user input the length of the array will increase or decrease, e.g. G9-G11 will become G9-G12 if there are four rows occupied with data. For all my other SUMming equations I have solved this and even came up with what I think is a perfectly valid solution for this one. The one problem is that it doesn't work when combined into a single equation.

My solution for the upper part of the fraction is this:

"A1"=SUMPRODUCT(INDIRECT("G9:G"&MIN(ROW(G9)+A8-1)),INDIRECT("H9:H"&MIN(ROW(H9)+A8-1)))

Where the user inputs the number of rows used in A8.

The solution for the lower part of the fraction is this:
"A2"=SUM(INDIRECT("G9:G"&ROW(G9)+A8-1))

[code]....

View 3 Replies View Related

INDIRECT Not Working For Dynamic Named Range In SUMPRODUCT

Feb 11, 2012

The formula is =SUMPRODUCT((Group="A")*(Project_Description="Long Term")*(Profile="B")*(INDIRECT(B9)="x"))

B9 through M9 have header text Jan_2012, Feb_2012...Dec_2012. Basically my aim is to get the above formula working before I drag it across so that the named ranges get picked up automatically from the headers.

Jan_2012 thru Dec_2012 are dynamic named ranges using INDEX (and not OFFSET as someone mentioned OFFSET is a volatile function).

The above SUMPRODUCT formula is giving me a #REF! error for the (INDIRECT(B9)="x") part. I know that because when I replace it with (Jan_2012="x") it works fine.

I realized while writing this that it may be because the named range in Jan_2012 is not the same size as that of other arrays. [but it is the same size - I've re-confirmed just now]

View 5 Replies View Related

Sumproduct -in Column A I Have Dates And In Column B I Have Names

Jan 30, 2008

in column a I have dates and in column b I have names.

eg

A1 = 1/1/08
A2 = 2/3/08
A3 = 3/1/08
A4 = 3/1/08

B1 = Jenny
B2 = Jenny
B3 = Jenny
B4 = Pat

I am trying to count the number of instances of "Jenny" in January.

I tried =sumproduct(A:A,>=39448

View 9 Replies View Related

Sumproduct With Value Or Text

Nov 27, 2013

My formula works fine:

In cell D12:
=SUMPRODUCT(('Raw data'!$L$25:$L$10958=Sheet1!$C$9)*('Raw data'!$C$25:$C$10958=Sheet1!$C12),('Raw data'!$H$25:$H$10958))

Sheet1!$C$9 = "Both"

But when i change C9 to i.e. 2013 or 2012....the data does not show anything only 0s?

i have attached a sample : sumprod_values_issue .xlsm‎

View 11 Replies View Related

Sumproduct & Text

Feb 5, 2007

I am trying to use the following formula:
=SUMPRODUCT('Raw Data'!$B$2:$B$10000=$A$1)

The reason why I am using sumproduct is b/c I have to add more to this formula. In column B on the Raw Data sheet there are years listed. The years are formatted as text. Is there a way to have this formula work without having to change the format of column B?

View 10 Replies View Related

Sumproduct For Numbers Within Text

Jan 16, 2014

How to use Sumproduct for the following?

Col1 Col2
5 1 mL
4 1.5 mL
5 2.5 mL
7 2 mL
2 1.5 mL

View 7 Replies View Related

Sumproduct With Text String

Aug 4, 2008

I have trying to write a sumproduct formula, however, the total sum amount that i get is "0", which i think the formula is not picking up the text.

=SUMPRODUCT(($Y$2:$Y$65536="Evaporative cooler*")*($Q$2:$Q$65536=$AM$5)*($N$2:$N$65536=6),($AA$2:$AA$65536))

Y column is the text column to find the Evaporative cooler, the Q is another column where i have write the text out at AM5, N is a number field. The only bracket that is not working is the Y. Can you please let me know if I did anything wrong to it?

There are 3 types of Evaporative cooler, 1) Evaporative cooler - phoenix, 2) Evaporative cooler - Cal, 3) Evaporative cooler - Brisa. I wondered if I can use "*" to select all of the Evaporative cooler, instead of listing all three.

View 14 Replies View Related

Sumproduct To Find Text

Nov 18, 2008

I recently had a lot of help using the sumproduct formula with isnumber datevalue added to it. I now want to do the same but rather than finding numbers, i want it to find text.

In my attached example. You can see that Jerry A has received three applciations, but 2 of them have been cancelled. How can i add the formula where it would only count Jerry A's applications, but where they have been cancelled i.e. the formula would show 2 not 3.

View 6 Replies View Related

Sumproduct #Value! Error Due To Text

Feb 26, 2007

I'm using this sumproduct and it works until a cell has text.
=SUMPRODUCT(($C7:$C16=$C$2)*($D7:F16))

I've tried =SUMPRODUCT(--($C7:$C16=$C$2),($D7:F16)) but got the #Value! error because of the D7:F16 range. If I changed it to become F7:F16, then it works. But...my original formula adds all values giving me a YTD result. If I changed the range to be only F7:F16, then all I get is just that column's data. I have January to December's data across the columns from D to F. Any ideas on keeping my D to F range? I've tried Transpose but it didn't work either.

View 9 Replies View Related

SUMPRODUCT Contains Text, Using Asterisk

Nov 9, 2009

Let's say I have two columns: Homeless (column A) & Services (column B). Homeless responses can be either "Y" or "N". Service can be "a", "b", or "ab". Here is an example:

Homeless Service
y a
y b
n a
y ab

I was hoping to use SUMPRODUCT to calculate clients who are homeless and receiving "a" service. I want to be able to calculate those receiving "a" service even if they are receiving "b" service, so I attempted to use asterisks like I have used prior with the COUNTIF function. Here is what I thought would work:
=SUMPRODUCT((A2:A5="y")*(B2:B5="*a*"))

And I expected it to return 2, but I found that I couldn't get the SUMPRODUCT to work with asterisks. I am able to get the results I want be creating a new column to sort the services into just those who are homeless and then using COUNTIF, but I'd rather come up with a more streamlined approach.

View 3 Replies View Related

Sumproduct To Return TEXT Value?

Dec 23, 2013

I written the formula below:

=SUMPRODUCT((B7:B9=$A1)*(E7:E9>=0),"CAP EXCEEDED","CAP Not breached")

B7:B9 = text and numerical values in format Year 1, Year 2 etc
E7: E9 = numerical values

what I am trying to achieve is if YEAR 1 for example is equal to cell A1 and the value in column E is greater than 0 then return statement "CAP Exceeded else return the statement CAP not breached.

View 6 Replies View Related

SUMPRODUCT To Return Text

May 1, 2008

The below table is a small extract from my data. I want the formula in C3 to return the contents of A4, that is, where Amount = D3, Policy = E3, Status A3. The following formula returns 0, because I'm trying to return a text string rather than a numerical value:....

View 9 Replies View Related

Wildcard For Text In Sumproduct

Aug 14, 2008

Tring to get the following spread sheet to calc column C using two criteria
Criteria 1 Column D must contain HC Investor anywhere
Criteria 2 Column A must be less than Reimbrusement - 11
The following formula in G2 appears to have an error in criteria 1 ....

View 9 Replies View Related

Text Wildcard In Sumproduct

Jun 15, 2009

I am having great difficulties in finishing this formula off.
It is a sumproduct containg many criterias, however one of them is to only add up a certain coloumn if another column begins with L&L...

The coloumd contains L&L and a number, but the number changes so i need the number to be a wildcard. I have tried ="L&L???" but this does not work.

=-SUMPRODUCT((Catagory="STK Retail")*(MONTH(Inv_Date)=3)*(YEAR(Inv_Date)=2008)*(How"Account")*(Order="L&L???")*(Stock))

View 9 Replies View Related

Sumproduct Using Text And Values

Jan 21, 2004

Is there a way to sum a list that contains both text and values using the SUMPRODUCT function? My efforts yielded the #VALUE! error. SUM and SUMIF will ignore the text but I have multiple criteria.

View 9 Replies View Related

SUMPRODUCT Use To Return A Text

May 11, 2006

If recently learned how to use the sumproduct function as a lookup for multiple criteria and it was working great, but I realize now all the values that I was returning were numbers. Is there a way to use the sumproduct under the same circumstances but to return a text value?

COL ACOL BCOL C
A1X
A2F
A3G
B1E
B2S

I would want the lookup with conditions of ColA=A and ColB=2, so the return should be "F"
=SUMPRODCUT(($A$1:$A$5="A")*($B$1:$B$5=2)*$B$1:$B$5).

View 9 Replies View Related

SUMPRODUCT With 2 Wildcard Text Match?

Feb 20, 2014

Here is what I have so far:

=SUMPRODUCT(--(ISNUMBER(SEARCH("Red",A9:A56))),B9:B56,L9:L56)/SUMPRODUCT(--(ISNUMBER(SEARCH("Red",A9:A56))),B9:B56)

So in column A is the text string i.e Big Red Chiquita Apple
Column B is the dollar value
Column C is another variable.

If I want to find "Red" and "Apple" ONLY within same cell. What would be my best bet?

View 5 Replies View Related

SUMPRODUCT With Today() And Text Criteria

Feb 19, 2010

Im attempting to add number of dates within a column (B). I have 2 criteria: Today and person "William". Im using the following and return results is "0"

A: Name
B: Date
C: Type

=SUMPRODUCT(--(Data!$B$2:$B$3100=TODAY()),--(Data!$A$2:$A$3100="William"),--(Data!$B$2:$B$3100))

Once I get this working I would like to adjust for last 30days and look at Type (C) = New

View 5 Replies View Related

Pull From Another Sheet And Sumproduct And TEXT

Oct 16, 2007

I have sheet 1 which has titles set up in column A. These titles are pulled from Sheet 2 (same column). I also have a date listed at the top of sheet 1 (in the same row as dates in sheet 2, but sheet 2 contains multiple columns for each quarter). I would like to set something up where if the date in sheet 1 matches the date in sheet 2 it pulls the corresponding values from the named rows in sheet 1. I tried using sumproduct but its not working b/c it wont take TEXT. Plus I want it to search across all of the columns, not just one.

Below is sheet 1 and I would like to be able to pull 1Q08 values from sheet 2 (which contains the values for every quarter). This is my formula (the first part is the date, the second is row titles, the third is the value (although its only pulling from one column, it s/b pulling from all) :

=SUMPRODUCT((Sheet2!B1:B6500=sheet1!B6)*(Sheet2!A1:A6500=Sheet2!A7)*(Sheet2!BA1:BA6500))

Is there someway to fix this, it would be a huge time saver.
--------------------------------------
1Q08
Revenue
Direct Costs
Gross Profit
Indirect Costs
Stock Based Compensation
Restructure Charge ....................

View 4 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved