SUMIF

[Alex]

I'm using a SUMIF FORMULA..but haveing problems cause of
the date. In one tab I have amounts for each date that is
formatted May 30, 2001. For example that date will have
100..so you have the idea..now on my Second tab, I have a
summary with Years going across on the top 2001 2002 2003
and with Months going down Jan Feb Mar..now the field
where they interesect..I made a coulmn that combines the
year and month together Jan2001...would look like that.
Now I use =sumif(the date range in first tab,year and
month of second tab(jan2001),the amounts from the first
tab). Now I think its the format that's the
problem..cause if I replace the dates with the first tab
with text jan2001 it works fine..I try converting
different format doesnt seem to be working. Maybe Im
going this the wrong way?..Please advise

 

[Domenic]

Hi Alex,

If you have your dates (true date values) and corresponding values in
Columns A and B of Sheet 1, and in Sheet 2 if you have your years going
across the first row starting in B1, and your months down Column A
starting in A2...

Put the following formula in B2 of Sheet 2, and copy across and down:

=SUMPRODUCT((TEXT(Sheet1!$A$1:$A$1000,"mmm")=Sheet2!$A2)*(YEAR(Sheet1!$A$
1:$A$1000)=Sheet2!B$1),Sheet1!$B$1:$B$1000)

Adjust the range accordingly.

Hope this helps!