sum with multiple arguments

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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.
 
=SUMPRODUCT((A12:A27={"Jan","Feb","Mar","Apr","May"})*(L12:L27))

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)
 
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
 
Back
Top