another SUMPRODUCT question

[Harvey Waxman]

In this formula:

=SUMPRODUCT(B8:B940,L8:L940)/SUM(B8:B940)

I want only the values in B & L where a corresponding cell in M is "yes"

I can't see where to put the IF(M8:M940="YES")

Thanks

 

[Carl Witthoft]

In this formula:

=SUMPRODUCT(B8:B940,L8:L940)/SUM(B8:B940)

I want only the values in B & L where a corresponding cell in M is "yes"

I can't see where to put the IF(M8:M940="YES")

Thanks

maybe an array formula

= sum (if( M8:M940="YES", product(B8:B940,L8:L940),0)) /sumif
(M8:M940="YES", B8:B940)

(all one formula)

Carl

 

[Harvey Waxman]

maybe an array formula

= sum (if( M8:M940="YES", product(B8:B940,L8:L940),0)) /sumif
(M8:M940="YES", B8:B940)

(all one formula)

When I enter your original: (some minor changes)
=SUM(IF(E8:E904="YES",PRODUCT(B8:B904,L8:L904),0))/SUMIF(E8:E904="YES",B8
:B904) I'm told it contains an error

Then I modified it to this:

=SUM(IF(E8:E904="YES",PRODUCT(B8:B904,L8:L904),0))/SUM(IF(E8:E904="YES",B
8:B904))

and I get a #NUM! error.

 

[JE McGimpsey]

One way:

=SUMPRODUCT(--(M8:M940="yes"),B8:B940,L8:L940)/SUMIF(M8:M940, "yes",
B8:B940)

 

[Harvey Waxman]

Thanks for the suggestion and it does as advertised!

So the unary minus is used when the array contains text to insure that
there are positive values in the calculation. It wasn't necessary in
the SUMIF part because the values in M weren't actually used in the
calculation. Is that right?

 

[JE McGimpsey]

So the unary minus is used when the array contains text to insure that
there are positive values in the calculation. It wasn't necessary in
the SUMIF part because the values in M weren't actually used in the
calculation. Is that right?

Not quite. See

http://www.mcgimpsey.com/excel/doubleneg.html

 

[Harvey Waxman]

Not quite. See

http://www.mcgimpsey.com/excel/doubleneg.html

SUMIF doesn't ignore non-numerics and SUMPRODUCT does.

 

[Carl Witthoft]

When I enter your original: (some minor changes)
=SUM(IF(E8:E904="YES",PRODUCT(B8:B904,L8:L904),0))/SUMIF(E8:E904="YES",B8
:B904) I'm told it contains an error

Then I modified it to this:

=SUM(IF(E8:E904="YES",PRODUCT(B8:B904,L8:L904),0))/SUM(IF(E8:E904="YES",B
8:B904))

and I get a #NUM! error.

You did enter it as an array formula?

Other than that, (I know you got a working solution from the wizards)
one of us should build up my putative formula in pieces and see what
step crashes.

 

[Carl Witthoft]

When I enter your original: (some minor changes)
=SUM(IF(E8:E904="YES",PRODUCT(B8:B904,L8:L904),0))/SUMIF(E8:E904="YES",B8
:B904) I'm told it contains an error

Then I modified it to this:

=SUM(IF(E8:E904="YES",PRODUCT(B8:B904,L8:L904),0))/SUM(IF(E8:E904="YES",B
8:B904))

and I get a #NUM! error.

Here's my formula, copied right out of a workbook:

=SUM(IF(A4:A13="yes",B4:B13*C4:C13,0))/SUM(IF(A4:A13="yes",B4:B13,0))

That worked perfectly. So I dunno why it didn't work for you except
maybe 'cause you left out the 'value_if_false' value in the denominator

 

[Harvey Waxman]

Here's my formula, copied right out of a workbook:

=SUM(IF(A4:A13="yes",B4:B13*C4:C13,0))/SUM(IF(A4:A13="yes",B4:B13,0))

That worked perfectly. So I dunno why it didn't work for you except
maybe 'cause you left out the 'value_if_false' value in the denominator

The unary minus formula that JE McG sent worked fine. I'll experiment
with yours too to see if it was I who made the error. Wouldn't be the
first time nor the last.

Thanks for the followup.