Solved

# Multiple Sum Products

Posted on 2011-05-13
174 Views
Hi,

Im trying to search a column where i need to sum multiple flags

this is failing

=SUMPRODUCT(--(\$C\$4:\$C\$5000="C/A"),--(\$C\$4:\$C\$5000="Liq",--(\$Z\$4:\$Z\$5000="61-90")))

How can i search the same column for multiple criteria?

Thanks
Seamus
0
Question by:Seamus2626

LVL 31

Expert Comment

For starters you are missig a bracket after LIQ, maybe just a typo in the question.

Alternatively try with an OR command like this:

=SUMPRODUCT(OR(--(\$C\$4:\$C\$5000="C/A"),--(\$C\$4:\$C\$5000="Liq")),--(\$Z\$4:\$Z\$5000="61-90")))

Cheers
Rob H
0

LVL 31

Expert Comment

The missing bracket has been added to the end, presumably automatically on confirming the entry.

Cheers
Rob H
0

LVL 31

Expert Comment

Which therefore means my suggestion has too many brackets because I copied from the question but inserted the bracket after the LIQ as well as the brackets for the OR statement but didn't remove the surplus from the end.

Cheers
Rob H
0

LVL 85

Accepted Solution

You can't use OR in SUMPRODUCT like that (or AND). Use:

=SUMPRODUCT(--(\$C\$4:\$C\$5000={"C/A","Liq"}),--(\$Z\$4:\$Z\$5000="61-90"))

or:

=SUMPRODUCT(((\$C\$4:\$C\$5000="C/A")+(\$C\$4:\$C\$5000="Liq")),--(\$Z\$4:\$Z\$5000="61-90"))
0

Author Closing Comment

That works well, thanks Rorya,

Thanks too Robenson

Cheers,
Seamus
0

LVL 85

Expert Comment

I should add - you should only use the curly brackets syntax if you only have one column that needs multiple criteria.
0

## Featured Post

A2 = A1 That kind of cell reference is relative.  If you copy it from A2 to B2, then B2 will get this: B2 = B1 That's all fine and good, but if you then insert a new row above row 2, you'll find: A3 = A1 B3 = B1 This is intentional. …
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
Viewers will learn the basics of slicers and timelines for both PivotTables and standard Excel tables in Excel 2013.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …