Sumif Three Criteria

[JimS]

I need to copy the following formula down multiple times throughout
the spreadsheet:

=SUMPRODUCT(--(K20:K35="x")*(N20:N35="w")*(S20:S35))

Like so:

=SUMPRODUCT(--(K42:K57="x")*(N42:N57="w")*(S42:S57))

But there are 16 rows of data in the first table and varying rows of
data in subsequent tables, so I get the #value error.

Sumif will work, but to my knowledge only with two criteria; can you
make sumif work with three criteria?

Or is there another way altogether?

Thanks

 

[JimS]

I think I might have answered my own question. In this formula

=SUMPRODUCT(--(K20:K35="x")*(N20:N35="w")*(S20:S35))

I changed the *(S20:S35)) at the end to ,(S20:S35)). By changing
from multiplication to a comma it seems to work. (Not exactly sure
why, but that's another topic.)

 

[Dave Peterson]

In general, I like this syntax:

=SUMPRODUCT(--(K20:K35="x"),(N20:N35="w"),(S20:S35))

It's kind of like the difference between:
=sum(s20:s35)
which ignores text in those cells
and
=s20+s21+s22+...+s35
which will result in an error if any of those cells contain text.

 

[JimS]

Thanks. Good suggestion, and understood.

In general, I like this syntax:

=SUMPRODUCT(--(K20:K35="x"),(N20:N35="w"),(S20:S35))

It's kind of like the difference between:
=sum(s20:s35)
which ignores text in those cells
and
=s20+s21+s22+...+s35
which will result in an error if any of those cells contain text.