3 criteria

[Mike B]

I have the following formula for adding numbers that match two criteri
that works fine
=SUMPRODUCT(--($E$125:$E$1084=AM4),--($AS$125:$AS$1084="yes"),$K$125:$K$1084)

I want to add a third criteria so I did this:
=SUMPRODUCT(--($E$125:$E$1084=AM4),--($E$125:$E$1084=AM34),--($AS$125:$AS$1084="yes"),$K$125:$K$1084)

and now the formula result is always 0

How can I add a third criteria to be checked before adding the matche
in column K?

Thanks
M

 

[Bernie Deitrick]

Mike,

This part:

--($E$125:$E$1084=AM4),--($E$125:$E$1084=AM34)

means that the values in column E must be equal to two _both_ of different cells. If you want it to
be equal to _either_ of the two cells, then add those terms together together:

=SUMPRODUCT(((--($E$125:$E$1084=AM4))+(--($E$125:$E$1084=AM34))),--($AS$125:$AS$1084="yes"),$K$125:$K$1084)

HTH,
Bernie
MS Excel MVP

 

[Mike B]

Worked like a charm, thank you very much!

Mike,

This part:

--($E$125:$E$1084=AM4),--($E$125:$E$1084=AM34)

means that the values in column E must be equal to two _both_ of
different cells. If you want it to
be equal to _either_ of the two cells, then add those terms together
together:

=SUMPRODUCT(((--($E$125:$E$1084=AM4))+(--($E$125:$E$1084=AM34))),--($AS$125:$AS$1084="yes"),$K$125:$K$1084)

HTH,
Bernie
MS Excel MVP


"Mike B" (e-mail address removed) wrote in message

I have the following formula for adding numbers that match two
criteria
that works fine

=SUMPRODUCT(--($E$125:$E$1084=AM4),--($AS$125:$AS$1084="yes"),$K$125:$K$1084)

I want to add a third criteria so I did this:

=SUMPRODUCT(--($E$125:$E$1084=AM4),--($E$125:$E$1084=AM34),--($AS$125:$AS$1084="yes"),$K$125:$K$1084)

and now the formula result is always 0

How can I add a third criteria to be checked before adding the
matches
in column K?

Thanks
MB