SUMPRODUCT with AND() or OR()

  • Thread starter Thread starter Dave
  • Start date Start date
D

Dave

Hi,

Is there an easy way to incorporate and AND() or or()
within the SUMPRODUCT function?

I want the following to take place within SUMPRODUCT:

sum values for 'x' where ('a'=2004 and 'b' = Dec) or
('a'=2005 and 'b' = Feb). I use cell ref's for the values
in the above examples.

Thanks.
 
Hi
try something like
=SUMPRODUCT((A1:A100=2004)*(B1:B100="Dec"),X1:X100)

or
=SUMPRODUCT(--(A1:A100=2004),--(B1:B100="Dec"),X1:X100)
 
Hi Dave,

Assuming column B is a string (if you DO have a real date instead, advise as the
solution could be simplified):

=SUMPRODUCT(--((A1:A100=2004)*(B1:B100="Dec")+
(A1:A100=2005)*(B1:B100="Feb")>0),X1:X100)

Regards,

Daniel M.
 
Assuming column B is a string (if you DO have a real date instead, advise as
the solution could be simplified):

=SUMPRODUCT(--((A1:A100=2004)*(B1:B100="Dec")+
(A1:A100=2005)*(B1:B100="Feb")>0),X1:X100)
Click to expand...
...

Picky: in this particular case the values in cols A and B are mutually
exclusive, so

=SUMPRODUCT((A1:A100=2004)*(B1:B100="Dec")+(A1:A100=2005)*(B1:B100="Feb"),
X1:X100)

would give the same results.
 
Back
Top