CODEWITHSUNDEEP
excel-formula
norden22
norden22

Reputation: 3

Use several "criterias" for SUMPRODUCT

I manage to sum all values based on one (1) criteria ("SIGN-") according to the following formula:

=SUMPRODUCT(--ISNUMBER(SEARCH("SIGN-";'1. Data'!$C:$C));'1. Data'!D:D)

This is based on the formula looking for the "SIGN-"-text in only one (1) column. Now, I would like the formula to also look for a certain text in column E in the '1. Data' tab.

I.e. I would like the formula to sum all values in column D as long as column C starts with the word "SIGN-" and column E="2017-12".

Have been trying to add the AND function to several places in the formula but I don´t manage to make it work.

Can you please help me out?

Upvotes: 0

Views: 76

Answers (1)

ImaginaryHuman072889
ImaginaryHuman072889

Reputation: 5205

Try this:

= SUMPRODUCT('1. Data'!D:D;(LEFT('1. Data'!C:C;5)="SIGN-")+0;('1. Data'!E:E="2017-12")+0)

The preferred solution would be to use SUMIFS but since the C column criteria is a bit trickier, the easier-to-use formula is with SUMPRODUCT even though it is not as efficient. That being said, it is recommended to reduce these full column references only to the end of the data. E.g. if you have 100 rows of data, change D:D to D1:D100, etc.

Upvotes: 1

Related Questions