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

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


--
Dav
------------------------------------------------------------------------
Dav's Profile: http://www.excelforum.com/member.php...o&userid=27107
View this thread: http://www.excelforum.com/showthread...hreadid=522071

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

Thanks - both of these work!!!



"AAMIFC" wrote:

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