Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Summing data based on certain cells
I have a spreadsheet that has data for each month. At the bottom of each
month is a summary of the data in four categories. What I need to do is for the data to be summed automatically, based on the four categories when I insert it for month X. I am currently using =SUMIF(INDEX($H:$H,N93):INDEX($H:$H,O93),N94,INDEX ($M:$M,N93):INDEX($M:$M,O93))+SUMIF(INDEX($H:$H,N9 3):INDEX($H:$H,O93),N94,INDEX($N:$N,N93):INDEX($N: $N,O93)) Whe Column H is where the category definition is for each line item N93 is the cell that tells the formula what the starting row is O93 is the cell that tells the formula what the ending row is N94 is the Category that is being summed in column H Column M and N is where the numbers are that are being summed. I have since migrated to Excel 2007 so the SUMIFS command is available now. My question is - is there a more elegant way of doing this? Also, I currently have to physically input the row number into N93 and O93. The problem is that if I insert data into a prior month, it obviously changes all the start and end rows for the months below and I have to fix them manually. Is there a way to fix the start and end rows absolutely so that if I do insert data in a prior month the reference in N93 and O93 will automatically update? |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Summing cells within a column based on cell fill color | New Users to Excel | |||
Summing data based on criteria | Excel Discussion (Misc queries) | |||
Conditionally summing cells based on conditions in other rows | Excel Worksheet Functions | |||
Summing Up Data Based on Codes | Excel Worksheet Functions | |||
Summing Data based on Its Category | Excel Discussion (Misc queries) |