SUMIF problem 1

[Roosters]

Hi,
Is there a way of finding the sum of a range that has to satisfy 2 criteria with the SUMIF function? I have a list labelled Type - Year - QTY as shown below, I need to find the sum of the QTY column when for example the TYPE is "A" and the year is 2009. The result should be 4.

TYPE - YEAR - QTY
A 12/07/08 1
B 12/07/08 2
A 12/07/09 3 <---
B 12/07/09 4
A 12/07/10 1
B 12/07/10 2
A 12/07/08 3
B 12/07/08 4
A 12/07/09 1 <---
B 12/07/09 2
A 12/07/10 3
B 12/07/10 4

Thanks in advance Phil

 

[SkipVought]

hi,

If you have 2007, use SUMIFS().

2003, use SUMPRODUCT. Change your column heading from Year to Dte. Use named ranges...
[tt
=SUMPRODUCT((Type="A")*(YEAR(Dte)=2009)*(QTY))
[/tt]


Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Roosters]

Hi Skip,

Works great, thanks for your time.

Phil

 

[Roosters]

Hey Skip
I have successfully used the SUMPRODUCT in 2003 but have now tried the SUMIFS() in 2007 with no luck. My problem if with the YEAR() part of the Dte range giving me an error warning
My formula is =SUMIFS(QTY,Type,D32,YEAR(Dte),E32)

Any help would be appreciated

Phil

 

[SkipVought]

What values are in D32 & E32, please?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Roosters]

Hi

D32 is text "A" and E332 is 2009 (format is General?)
Phil

 

[SkipVought]

[tt]
=SUMIFS(QTY,TYPE,D32,Dte,">="&TEXT(E32,"yyyy/mm/dd"),Dte,"<="&TEXT(F32,"yyyy/mm/dd"))
[/tt]
where E32 = 1/1/2009 and F32 = 12/31/2009.



Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]