Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
can you nest sumif functions ?
I am trying to nest sumif function with no luck.
I have a table of data with several columns. I need to look at the month column first and see if it's "January", then look at the "name" column to see if a specific person performed the procedures in that month, then finally add the number of procedures this person performed in January. I want to have a Data sheet that contains all this information, then without using pivot tables set up a permanent spreadsheet that puts names of people as rows and months as column headers, then the number of procedures as the data. Can anybody help ? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
can you nest sumif functions ?
Hi
Assuming that this data is held on Sheet1 and Column A holds Month, B holds Name and C holds Count of functions. On Sheet2 set up a Matrix with January in B1 through to December in M1 Place your Names in A2:Ann where nn is the last row for the range of people concerned. In B2 enter =SUMPRODUCT((Sheet1!$A$2:$A$100=B$1)* (Sheet1!$B$2:$B$100=$A2)*Sheet1$C$2:$C$100)) This assumes that your entries in column A of Sheet1 are text entries. If they are true Excel Dates, then amend formula to =SUMPRODUCT((TEXT(Sheet1!$A$2:$A$100,"mmmm")=B$1)* (Sheet1!$B$2:$B$100=$A2)*Sheet1$C$2:$C$100)) Copy formula across and down to fill your matrix. Change ranges in formula to suit range of data on Sheet1 but do ensure all ranges are of equal length. -- Regards Roger Govier "SCarleton" wrote in message ... I am trying to nest sumif function with no luck. I have a table of data with several columns. I need to look at the month column first and see if it's "January", then look at the "name" column to see if a specific person performed the procedures in that month, then finally add the number of procedures this person performed in January. I want to have a Data sheet that contains all this information, then without using pivot tables set up a permanent spreadsheet that puts names of people as rows and months as column headers, then the number of procedures as the data. Can anybody help ? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
can you nest sumif functions ?
Perhaps something along these lines:
In B1 across: January, February ... In A2 down: Names of staff Then in B2, copied across and filled down to populate: =Sumproduct((Staff=$A2)*(Month=B$1),Procedures) where Staff, Month, Procedure would be identically structured defined ranges referring to the relevant source data -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "SCarleton" wrote: I am trying to nest sumif function with no luck. I have a table of data with several columns. I need to look at the month column first and see if it's "January", then look at the "name" column to see if a specific person performed the procedures in that month, then finally add the number of procedures this person performed in January. I want to have a Data sheet that contains all this information, then without using pivot tables set up a permanent spreadsheet that puts names of people as rows and months as column headers, then the number of procedures as the data. Can anybody help ? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to nest functions with SUMIF function in excel | Excel Worksheet Functions | |||
How can I nest ISTEXT and IF functions? | Excel Worksheet Functions | |||
How do I nest these 3 IF functions? | Excel Worksheet Functions | |||
Why not nest more than 7 functions in Excel formula??? | Excel Worksheet Functions | |||
How can I nest more than seven functions in MS Excel? | Excel Worksheet Functions |