SUMIF conditions

[Guest]

Hi
I have a work sheet with a number of years work by month and I would like to
sum all the like months depending on user input. I can do this with a SUMIF,
however the year is linked to the name tag. Eg

March 04 10
April 04 8
......
March 05 15
April 05 4
......
March 06 20
April 06 0
......

I would like to enter:
March
and return 45. I then enter April and return 12 as the answer. Is there
away to place a restriction on the array to be searched to look at say the
first three char?

thanks in Advance

 

[Dav]

It can be done, but it is not entirely clear from your example how your
data is stored. Is the March 06 a date or a text string?

If it is a date and the dates are stored in b2:b5 and the values you
wish to sum are in c2:c5 try
=SUMPRODUCT((MONTH(B2:B5)=3)*(C2:C5))
3 being march

Or if you want to type mar
=SUMPRODUCT((TEXT(B2:B5,"mmm")="Mar")*(c2:c5))


If it is text
=SUMPRODUCT((LEFT(b2:b5,3)="Mar")*(c2:c5))

If you need to sum for each month you could just put the months in a
cells and reference that cell rather than type mar, apr etc

Regards
Dav

 

[prabhuraaman]

Assuming the months are stored in date format
Let the range of month be a1:a50
let the range of tag be b1:b50
Let the cell where you wish to enter your value be c1

Then the array formula would be
{=SUM(IF(TEXT(A1:A50,"mmmm")=C1,B1:B50))}
Enter the formula and press ctrl+shift+enter to convert it into an
array formula

Substitute range a1:a50 and b1:b50 with your range
Instead of c1 you would directly edit as "march".
In case the values are stored as text simply change the forumula as
{=SUM(IF(A1:A50)=C1,B1:B50))}


For more,post your questions on
http://groups.google.co.in/group/answers-for-everything

 

[Guest]

Thanks - both of these work!!!