Sumproduct using text and values

[BuzzG]

Is there a way to sum a list that contains both text and values using the SUMPRODUCT function? My efforts yielded the #VALUE! error. SUM and SUMIF will ignore the text but I have multiple criteria. Any ideas or threads to follow?
BuzzG

 

[just_jon]

Can you post your current formula with descriptions of the ranges?

 

[Aladin Akyurek]

Is there a way to sum a list that contains both text and values using the SUMPRODUCT function? My efforts yielded the #VALUE! error. SUM and SUMIF will ignore the text but I have multiple criteria. Any ideas or threads to follow?
BuzzG

Depends on your data & what you want to compute. Maybe you can post a small sample along with the desired computation.

 

[BuzzG]

Sample Formula:

=SUMPRODUCT((C4:C8="RENEW")*(D4:D8="John")*(J4:J8))
Where J4:J8 contains both text and values.

 

[just_jon]

Does this work --

=SUMPRODUCT((C4:C8="RENEW")*(D4:D8="John"),--(J4:J8))

 

[BuzzG]

No, but this does:

=SUMPRODUCT((C4:C8="RENEW")*(D4:D8="John"),(J4:J8))

Changing the last multiply to a comma seemed to do the trick.

Thanks for your help.
BuzzG

 

[just_jon]

 

[Aladin Akyurek]

Sample Formula:

=SUMPRODUCT((C4:C8="RENEW")*(D4:D8="John")*(J4:J8))
Where J4:J8 contains both text and values.

Quite a coincidence. I just posted something at worksheet.functions explaining why it's better to use, whenever possible, the comma syntax of SumProduct. So, try...

=SUMPRODUCT(--(C4:C8="RENEW"),--(D4:D8="John"),(J4:J8))

or, equivalently...

=SUMPRODUCT((C4:C8="RENEW")+0,(D4:D8="John")+0,(J4:J8))

The explanation is that just like SUM, the Sum bit of SUMPRODUCT ignores text values in the range to sum if we adhere to its native, comma syntax.

 

[just_jon]

I ****-umed* it was a text-formatted numeric problem in the lqst range. Good evening, Aladin -- no more *, correct?

 

[just_jon]

And worksheet.functions is ?