• Status: Solved
• Priority: Medium
• Security: Public
• Views: 194

# Multiple Sum Products

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
Seamus2626
• 3
• 2
1 Solution

Finance AnalystCommented:
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

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

Cheers
Rob H
0

Finance AnalystCommented:
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

Commented:
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 Commented:
That works well, thanks Rorya,

Thanks too Robenson

Cheers,
Seamus
0

Commented:
I should add - you should only use the curly brackets syntax if you only have one column that needs multiple criteria.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.