sumproduct formula on steroids
 

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

sumproduct formula on steroids
 

Try this:

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

--
Biff
Microsoft Excel MVP


"EG" wrote in message
...
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

sumproduct formula on steroids
 

Another one:
=SUMPRODUCT((("ES"=B3:B6)+("ZN"=B3:B6))*E3:E6*G3:G 6)
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:17,800 Files:359 Subscribers:56
xdemechanik
---
"EG" wrote:
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

sumproduct formula on steroids
 

Sorry, dismiss the earlier. It's not correct.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:17,800, Files:359, Subscribers:56
xdemechanik
---

sumproduct formula on steroids
 

I think you missed the F*G condition:

If ...=ES ... multiply the value of E3 x G3.
If ...=ZN ... multiply F3 x G3.

--
Biff
Microsoft Excel MVP


"Max" wrote in message
...
Another one:
=SUMPRODUCT((("ES"=B3:B6)+("ZN"=B3:B6))*E3:E6*G3:G 6)
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:17,800 Files:359 Subscribers:56
xdemechanik
---
"EG" wrote:
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

sumproduct formula on steroids
 

I think you missed the F*G condition:
Yes I did. Have posted to dismiss my earlier response.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:17,800, Files:359, Subscribers:56
xdemechanik
---

sumproduct formula on steroids
 

Try this:

=SUMPRODUCT((B3:B6={"ES","ZN"})*(E3:F6*G3:G6))


"EG" wrote:

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

sumproduct formula on steroids
 

This one's a few keystrokes shorter:

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

--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
Try this:

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

--
Biff
Microsoft Excel MVP


"EG" wrote in message
...
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

sumproduct formula on steroids
 

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

"Teethless mama" wrote:

Try this:

=SUMPRODUCT((B3:B6={"ES","ZN"})*(E3:F6*G3:G6))


"EG" wrote:

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

sumproduct formula on steroids
 

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.

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 ToolsOptionsView tabuncheck Zero valuesOK. 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 FormatConditional 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


--
Biff
Microsoft Excel MVP


"EG" wrote in message
...
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

"Teethless mama" wrote:

Try this:

=SUMPRODUCT((B3:B6={"ES","ZN"})*(E3:F6*G3:G6))


"EG" wrote:

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

sumproduct formula on steroids
 

thanks very much for the help. got it! EG

"T. Valko" wrote:

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.

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 ToolsOptionsView tabuncheck Zero valuesOK. 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 FormatConditional 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


--
Biff
Microsoft Excel MVP


"EG" wrote in message
...
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

"Teethless mama" wrote:

Try this:

=SUMPRODUCT((B3:B6={"ES","ZN"})*(E3:F6*G3:G6))


"EG" wrote:

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

sumproduct formula on steroids
 

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"EG" wrote in message
...
thanks very much for the help. got it! EG

"T. Valko" wrote:

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.

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 ToolsOptionsView tabuncheck Zero valuesOK. 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 FormatConditional 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


--
Biff
Microsoft Excel MVP


"EG" wrote in message
...
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

"Teethless mama" wrote:

Try this:

=SUMPRODUCT((B3:B6={"ES","ZN"})*(E3:F6*G3:G6))


"EG" wrote:

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