Sum data in a column if multi creteria met

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
 
T

T. Valko

Try this...

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

Teethless mama

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)
 
T

tywlam

Thanks both. They work!

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

David Biddulph

=SUMPRODUCT(--(CEILING(MONTH(A1:A4)/3,1)=2),(YEAR(A1:A4)=2009)+(YEAR(A1:A4)=2010),--(B1:B4="XX"),C1:C4)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Multiple Creteria Lookup(Between Dates) 8
Count based on date criteria 1
if cell is blank then calculate how many days? 4
Highlight rows if column A matches but B is different 0
How to look up a value in a list and sum multiple corresponding va 2
Number of hrs worked in a week, based on dates in a different column 1
Checking data in 2 columns against a further 2 columns.... 1
Sum of values in column A based on 0/1 of column B 2

Top