Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Count occurances of multiple arguments | Excel Discussion (Misc queries) | |||
Conditional Statement with Multiple Arguments | Excel Worksheet Functions | |||
Multiple Arguments - New Twist | Excel Worksheet Functions | |||
Multiple Arguments | Excel Worksheet Functions | |||
Multiple vlookup arguments | Excel Worksheet Functions |