sumproduct formula on steroids

E

EG

Here is my problem: I want to write a formula in cell P11 that includes
information from columns B,E,F, and G. In column B, I have two types of data
(ZN and ES). If the value of B3=ES, then i want to multiply the value of E3
x G3. If B3 = ZN, i want to multiply F3 x G3. Then i want to add the sum of
the ZN cells with the sum of the ES cells so that this total is in P11.

B E F G
3 ES 6 5 2
4 ZN 6 5 1
5 ES 6 5 4
6 ZN 6 5 3

B3=ES, thus E3 x G3 or 6x2=12
B4=ZN, thus F4 x G4 or 5x1=5
B5=ES, thus E5 x G5 or 6x4=24
B6=ZN, thus F6 x G6 or 5x3=15

Then in cell P11 i want the sum of 12+5+24+15.

how can i do this?

Many thanks!

EG
 
T

T. Valko

Try this:

=SUMPRODUCT(--(B3:B6="ES"),E3:E6*G3:G6)+SUMPRODUCT(--(B3:B6="ZN"),F3:F6*G3:G6)
 
T

T. Valko

This one's a few keystrokes shorter:

=SUMPRODUCT(((B3:B6="ES")*E3:E6*G3:G6)+((B3:B6="ZN")*F3:F6*G3:G6))
 
E

EG

these formulas all work in a blank spread sheet even if i increase the range
from 6 to 325. however, when i try this in my working spread sheet, with the
range increased to 325, it gives me the error "#value!" in cell P11.

I have values in rows 3-11 for columns B, E,F, and G. and i also have
values in cells E12 and F12, but not in G12 (it is blank). If i make the
range 3-12, the formula works. if the range is more than this, it gives me
the above error. I do have formulas in the cells E, and F rows 13-325 so i
am wondering if the error message has something to do with these formulas not
being numbers that the formula in P11 can read.

does this make sense? If this is correct, any thoughts on how to make the
P11 formula not "read" the formulas in the "Blank" cells E and F rows 13-325?

Thanks! almost got it...

EG
 
T

T. Valko

I do have formulas in the cells E, and F rows 13-325
so i am wondering if the error message has something
to do with these formulas not being numbers that the
formula in P11 can read.
Click to expand...

If those formulas return formula blanks ("") then yes, that is the problem.

You'd have change the formulas to return 0, then you can hide the 0s if you
don't want to see them.

A couple of ways to hide the 0s:

Goto the menu Tools>Options>View tab>uncheck Zero values>OK. This applies to
the *entire sheet*. There may be other calculations that return 0 and you'd
want to see those so this might not be the best solution.

Use conditional formatting to hide the 0s...

Select the range of cells in question.
Assume this range is E7:G10
Goto the menu Format>Conditional Formatting
Select the Formula Is option
Enter this formula in the little box on the right:
=E7=0
Click the Format button
Set the font color to be the same as the background color
OK out
 
E

EG

thanks very much for the help. got it! EG

T. Valko said:
If those formulas return formula blanks ("") then yes, that is the problem.

You'd have change the formulas to return 0, then you can hide the 0s if you
don't want to see them.

A couple of ways to hide the 0s:

Goto the menu Tools>Options>View tab>uncheck Zero values>OK. This applies to
the *entire sheet*. There may be other calculations that return 0 and you'd
want to see those so this might not be the best solution.

Use conditional formatting to hide the 0s...

Select the range of cells in question.
Assume this range is E7:G10
Goto the menu Format>Conditional Formatting
Select the Formula Is option
Enter this formula in the little box on the right:
=E7=0
Click the Format button
Set the font color to be the same as the background color
OK out
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

SUMPRODUCT ??? 5
Counting formula 1
formula setup for number combinations 13
SUM formula after HLOOKUP result true 9
Best/Worst grade 8
sumproduct while number of added fields is changing? 2
Editing Lookup Formula Error 6
Sum if and with multiple criteria 18

Top