SUMPRODUCT issues

[jdEXCEL777]

Hello Everyone. I need help with the following SUMPRODUCT formula:

=SUMPRODUCT(($C$4:$C$13*$E$4:$E$13)*($B$4:$B$13="AA")*($A$4:$A$13="Landings"))

What it's basically doing is that it's summing the product of content of array in column C with content of array in column E, provided that the content of array in column B is "AA" and content of array in column A is "Landings."

The problem is that I am having a #Value error. I think the reason why is because some of the cells in the array C4:C13 contain text. And because I am using the asterix " * " at the beginning of the formula ($C$4:$C$13*$E$4:$E$13) to multiply, it is giving me an error. I know that because when I delete the text from the C4:C13 array, it works perfectly, just the way I want it. However, I need to leave the text in that array. Is there anything I can do to have the formula skip or ignore the cells containing text in the C4:C13 range and give me the result I am looking for? If I can figure this out, it would save me lots of time.
PLEASE HELP!!!

Thank you in advance.

 

[bstory84]

try this

=SUMIFS(C4:C13,A4:A13,"Landings",B4:B13,"AA")+SUMIFS(E4:E13,A4:A13,"Landings",B4:B13,"AA")

 

[AhoyNC]

Or change your formula to:

=SUMPRODUCT($C$4:$C$13,$E$4:$E$13,--($B$4:$B$13="AA"),--($A$4:$A$13="Landings"))

 

[bstory84]

Or change your formula to:

=SUMPRODUCT($C$4:$C$13,$E$4:$E$13,--($B$4:$B$13="AA"),--($A$4:$A$13="Landings"))

@AhoyNC
Although your formula does operate correctly for numbers it will error with text. the OP did specify that they had text in the ranges they wish to SUM.

 

[AhoyNC]

@bstory84
See below. The version I posted seems to work with text in column C.

Excel Workbook
	A	B	C	D	E
3	202
4	Landings	AA	1		2
5	Landings	G	2		3
6	rt	D	3		4
7	ui	AA	io		5
8	Landings	AA	ty		6
9	we	TY	6		7
10	q	E	7		8
11	ty	AA	8		9
12	Landings	AA	9		10
13	Landings	AA	10		11
Sheet

 

[Aladin Akyurek]

SUMPRODUCT, just like SUM, essentially ignores text in the sum range if invoked with the comma syntax (what AhoyNC does)...

=SUMPRODUCT($C$4:$C$13,$E$4:$E$13,--($B$4:$B$13="AA"),--($A$4:$A$13="Landings"))

The * syntax cannot ignore non-numeric text values in the sum range.

 

[bstory84]

Thank you both AhoyNC & Aladin for setting me straight. I'm sure i am incorrect but i assumed they wanted the SUM (addition) and not the Multiplied result. I apologize for any confusion i caused.

 

[jdEXCEL777]

AhoyNC, YOU ARE THE MAN!!!

The syntax you proposed works perfectly. EXACTLY what I was looking for . THANK YOU SO MUCH. However, could you please explain to me how it works? What I mean is that I understand " $C$4:$C$13,$E$4:$E$13 " basically multiplies the sum of the product of those arrays, and replacing the asterix with the comma solved the #VALUE error. However, how does the " ,--($B$4:$B$13="AA"),--($A$4:$A$13="Landings") " portion of the formula work? How do the two hyphens or minus signs work in terms of restricting the calculations to cells containing "AA" and "Landings" ?

Please explain it to me. Aside from that, it works beautifully. THANK YOU AGAIN AhoyNC.

 

[dreid1011]

the --() syntax forces TRUE/FALSE results to return as 1/0. So, in the sumproduct, anything that would return false is 0.

 

[AhoyNC]

Glad that worked for you.
The double negative signs work this way.

$B$4:$B$13="AA" will return the following array (based on the above example).
{TRUE;FALSE;FALSE;TRUE;TRUE;FALSE;FALSE;TRUE;TRUE;TRUE}

When we put the double negative in front it's the same as multiplying the array by 1 which will change the TRUE & False to 1 & 0.
So --($B$4:$B$13="AA") returns.
{1;0;0;1;1;0;0;1;1;1}
If a row is not equal to AA then it gets multiplied by 0 in the SUMPRODUCT.
Hope that helps.