sum with multiple arguments
 

I want to add the values of all of the cells from another table, up until the
date selected. To explain, I have a table with values for each month of the
year and would like to add all of the figures together to get the total on a
seperate summary worksheet. That is not the problem. But how do I tell the
computer to only add up certain cells, say from january until may, even
though I have data going until september? I was hoping i could do this with a
lookup function (i.e. =sum('Sheet 1'!b23:hlookup(E2,'sheet 1'!A12:N27,12,
false) - where sheet 1 has the values in row 23 - 12 rows down from the names
of the months and cell e2= the month that i want the computer to sum up to
[may]) but this is not working. any suggestions.
please explain clearly - i am not so familiar with all of the functions.
Thanks for the help.

sum with multiple arguments
 

=SUMPRODUCT((A12:A27={"Jan","Feb","Mar","Apr","May "})*(L12:L27))

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"hotelmasters" wrote in message
...
I want to add the values of all of the cells from another table, up until
the
date selected. To explain, I have a table with values for each month of
the
year and would like to add all of the figures together to get the total on
a
seperate summary worksheet. That is not the problem. But how do I tell
the
computer to only add up certain cells, say from january until may, even
though I have data going until september? I was hoping i could do this
with a
lookup function (i.e. =sum('Sheet 1'!b23:hlookup(E2,'sheet 1'!A12:N27,12,
false) - where sheet 1 has the values in row 23 - 12 rows down from the
names
of the months and cell e2= the month that i want the computer to sum up to
[may]) but this is not working. any suggestions.
please explain clearly - i am not so familiar with all of the functions.
Thanks for the help.

sum with multiple arguments
 

Try:

=SUMPRODUCT(--MONTH(A1:A100)=1),(B1:B100))

Column A contains dates
Column B data to be summed

The above will sum data for January (assuming there is only one year)

=SUMPRODUCT(--MONTH(A1:A100)=3),(--MONTH(A1:A100)<=6),(B1:B100))

This will sum March to June inclusive

Change ranges to suit and you can put the constants in cells ...

=SUMPRODUCT(--MONTH(A1:A100)=E2),(B1:B100))


HTH

"hotelmasters" wrote:

I want to add the values of all of the cells from another table, up until the
date selected. To explain, I have a table with values for each month of the
year and would like to add all of the figures together to get the total on a
seperate summary worksheet. That is not the problem. But how do I tell the
computer to only add up certain cells, say from january until may, even
though I have data going until september? I was hoping i could do this with a
lookup function (i.e. =sum('Sheet 1'!b23:hlookup(E2,'sheet 1'!A12:N27,12,
false) - where sheet 1 has the values in row 23 - 12 rows down from the names
of the months and cell e2= the month that i want the computer to sum up to
[may]) but this is not working. any suggestions.
please explain clearly - i am not so familiar with all of the functions.
Thanks for the help.