Sumproduct w/name range not working

[hrm1220]

I've gone through the threads on this site and haven't found anything that could help me w/the sumproduct.

I created name ranges using the following formula in the "Refers to Box":
OFFSET('F'!$A$1,1,MATCH("Cost",'F'!$A$1:$DR$1,0)-1,5000)
the name for this one is FCost
OFFSET('F'!$A$1,1,MATCH("WW",'F'!$A$1:$DR$1,0)-1,5000)
the name for this one is FWW
OFFSET('F'!$A$1,1,MATCH("Event",'F'!$A$1:$DR$1,0)-1,5000)
the name for this one is FEST


The sumproduct formula (which is in a different sheet then the Name ranges created above) that I'm having trouble with is the following:
=SUMPRODUCT((--(FWW=B3)*(FCost)))
seems simple, right?
Well I get an N/A, but if I change this to a sumif formula =SUMIF(FWW,B3,FCost) it works just fine.
The reason for using the sumproduct is that I'll have 2 different criteria which should look like:
=SUMPRODUCT((FEST="Install")*(FWW=B3)*(FCost))

any help would be appreciated.

 

[AnotherHiggins]

First thing that comes to mind is that each section of the SumProduct must contain the same number of rows.

For exampls - this would work:
[tab]=sumproduct(--(A1:A[blue]10[/blue]="A")*--(B1:B[blue]10[/blue]="B"))
but this wouldn't:
[tab]=sumproduct(--(A1:A[blue]10[/blue]="A")*--(B1:B[red]11[/red]="B"))

Also, use a double uriry (two minus signs in a row - as seen in example above) to ensure that the results of each section is a number.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[SkipVought]

The MATCH function looks up in ONE COLUMN. You have defined MULTIPLE COLUMNS AR...
[tt]
MATCH("Cost",'F'!$A$1:$DR$1,0)
[/tt]
Will "Cost", not ALWAYS be in the same column?

You might check out Insert > Name > Create - Create name in TOP row.



Skip,

_{Did you hear what happened when the OO programmer lost his library?...
He's now living in OBJECT poverty!}

 

[hrm1220]

sorry about that. The headings on the other sheet will not change, but which column they are in could change (I don't own this part of the data and this is why I did the offset/match function for the name range)

 

[SkipVought]

Then select ALL the data and use Insert > Name > Create - Create names in top row, and use the COLUMN HEADING NAMES as your Range Names...

=SUMIF([b]WW[/b],B3,[b]Cost[/b])

Skip,

_{Did you hear what happened when the OO programmer lost his library?...
He's now living in OBJECT poverty!}

 

[hrm1220]

ok. well i did that and my sumproduct still doesn't work (the sumif worked just fine)I still receive #N/A. I don't understand why this works for sumif but not for the sumproduct

 

[SkipVought]

Please post the sumproduct that does NOT work and explain how you named your ranges.

Skip,

_{Did you hear what happened when the OO programmer lost his library?...
He's now living in OBJECT poverty!}

 

[hrm1220]

i did that in the begining of the post:

I created name ranges using the following formula in the "Refers to Box":
OFFSET('F'!$A$1,1,MATCH("Cost",'F'!$A$1:$DR$1,0)-1,5000)
the name for this one is FCost
OFFSET('F'!$A$1,1,MATCH("WW",'F'!$A$1:$DR$1,0)-1,5000)
the name for this one is FWW
OFFSET('F'!$A$1,1,MATCH("Event",'F'!$A$1:$DR$1,0)-1,5000)
the name for this one is FEST


The sumproduct formula (which is in a different sheet then the Name ranges created above) that I'm having trouble with is the following:
=SUMPRODUCT((--(FWW=B3)*(FCost)))

 

[SkipVought]

...and I explained that MATCH does not work on a ROW range!!!

Use the naming technique that I described and then
[tt]
=SUMPRODUCT((--(WW=B3)*(Cost)))
[/tt]


Skip,

_{Did you hear what happened when the OO programmer lost his library?...
He's now living in OBJECT poverty!}

 

[SkipVought]

I am sorry that I miss-spoke.

Hang on!

Skip,

_{Did you hear what happened when the OO programmer lost his library?...
He's now living in OBJECT poverty!}

 

[SkipVought]

Sorry to misslead you.

The OFFSET formulas work just fine.

However, if your have any INVALID values in either column (ww or cost) then SUMPRODUCT will return n/a.

You can use the AUtoFilter to FIND such values.

Skip,

_{Did you hear what happened when the OO programmer lost his library?...
He's now living in OBJECT poverty!}

 

[hrm1220]

ok. I hate it when I don't look for the obvious. Thx Skip for showing the error of my way. I would like to know why "SUMIF" ignores the "N/A" and "Sumproduct" doesn't.

 

[xlbo]

Because SUMIF only looks for a discreet set of values to sum against. If it is #N/A then it just gets excluded from the list to sum against

SUMPRODUCT creates a matrix of 1s and 0s (trues and falses) for each set of criteria in the formula. It then uses this matrix to sum up any values at the end. a #N/A cannot be represented as true / false and therefore errors out the whole formula

=SUMPRODUCT((FEST="Install")*(FWW=B3)*(FCost))
would generate something like

1 0 15
1 1 10
1 1 15
0 1 5
0 0 10

All the columns are multiplied together to get the results which are then summed together:

0
10
15
0
0
=25

if you have a #N/A in there, anything multiplied by #N/A = #N/A so that is the result of the formula


Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question