Sum data in a column if multi creteria met

  • Thread starter Thread starter tywlam
  • Start date Start date
T

tywlam

Can anyone help me to use functions to solve the following problem:-


A B C
1 02/02/2009 XY 200
2 04/05/2009 XX 20000
3 30/06/2009 XX 100
4 08/09/2009 YY 50

I want to sum the data in Column C if the datas in same rows fall in 2nd
quarter in Column A and they are XX in Column B. How do I write use the
functions to get the correct answer. The answer should be 20100
(=20000+100). Please help! Thanks
 
Try this...

=SUMPRODUCT(--(MONTH(A2:A5)>=4),--(MONTH(A2:A5)<=6),--(B2:B5="xx"),C2:C5)
 
Try one of these:

=SUMPRODUCT(--(CEILING(MONTH(A1:A4)/3,1)=2),--(B1:B4="XX"),C1:C4)
or
=SUMPRODUCT((MONTH(A1:A4)={4,5,6})*(B1:B4="XX")*C1:C4)
 
Thanks both. They work!

What if the dates fall into the different years but I need one or two
specified years only?
 
Back
Top