Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Subtotalling after multiple criteria has been met
Hello. I'd appreciate a nudge in the right direction with this issue, as I've
been searching but I can't seem to solve this on my own. I'm trying to create a formula that goes through a list of data, and subtotals (sum) all entries only if columns A, B and C each equal a particular text string. Each string is different. Column E is where it needs to sum the totals from. This is a sample of sheet A (raw data). Market Type Job Total Australia AA Pink Car AA 295 Australia AA Pink Car AA 99 Australia RR Pink Car RR 30 Australia AA AUS Test GPC AA 1357 Australia RR AUS Test RR 3 Australia AA Pink Car AA 99 Australia AA Pink Car AA 99 Australia ZZ AUS Test BB / ZZ 456 India ZZ India ZZ Cat 3 India ZZ India ZZ Mouse 9 India AA India Calcium AA 200 I've created a Sheet that has a table with the criteria, waiting for the totals to be entered: Australia AA Pink Car AA (formula). I've tried playing around with SUMIF, and SUBTOTAL, tried nesting a combination of IF and AND statements, but it's becoming apparent to me that I have no idea of what I'm doing. Any help would be much appreciated. Thanks. - Cristian. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Subtotalling after multiple criteria has been met
Try this:
Use cells to hold the string criteria: G1 = string1 H1 = string2 I1 = string3 Then: =SUMPRODUCT(--(A1:A10=G1),--(B1:B10=H1),--(C1:C10=I1),E1:E10) -- Biff Microsoft Excel MVP "Cristian" wrote in message ... Hello. I'd appreciate a nudge in the right direction with this issue, as I've been searching but I can't seem to solve this on my own. I'm trying to create a formula that goes through a list of data, and subtotals (sum) all entries only if columns A, B and C each equal a particular text string. Each string is different. Column E is where it needs to sum the totals from. This is a sample of sheet A (raw data). Market Type Job Total Australia AA Pink Car AA 295 Australia AA Pink Car AA 99 Australia RR Pink Car RR 30 Australia AA AUS Test GPC AA 1357 Australia RR AUS Test RR 3 Australia AA Pink Car AA 99 Australia AA Pink Car AA 99 Australia ZZ AUS Test BB / ZZ 456 India ZZ India ZZ Cat 3 India ZZ India ZZ Mouse 9 India AA India Calcium AA 200 I've created a Sheet that has a table with the criteria, waiting for the totals to be entered: Australia AA Pink Car AA (formula). I've tried playing around with SUMIF, and SUBTOTAL, tried nesting a combination of IF and AND statements, but it's becoming apparent to me that I have no idea of what I'm doing. Any help would be much appreciated. Thanks. - Cristian. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Subtotalling after multiple criteria has been met
Cristian wrote:
Hello. I'd appreciate a nudge in the right direction with this issue, as I've been searching but I can't seem to solve this on my own. I'm trying to create a formula that goes through a list of data, and subtotals (sum) all entries only if columns A, B and C each equal a particular text string. Each string is different. Column E is where it needs to sum the totals from. [snipped] Have you tried a pivot table? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Subtotalling after multiple criteria has been met
Hi,
And the steps for a pivot table a Here are the basic steps for setting up a pivot table: 1. Select all the data with one row of titles and choose Data, PivotTable and PivotChart Report 2. Click Next twice 3. Click Layout and drag the Market field button to the Row area 4. Drag the Job field button to the Row area 5. Drag the Type button to the Row area 6. Drag the Total field button to the Data area 7. Click OK, Finish. If this helps, please click the Yes button Cheers, Shane Devenshire "Cristian" wrote: Hello. I'd appreciate a nudge in the right direction with this issue, as I've been searching but I can't seem to solve this on my own. I'm trying to create a formula that goes through a list of data, and subtotals (sum) all entries only if columns A, B and C each equal a particular text string. Each string is different. Column E is where it needs to sum the totals from. This is a sample of sheet A (raw data). Market Type Job Total Australia AA Pink Car AA 295 Australia AA Pink Car AA 99 Australia RR Pink Car RR 30 Australia AA AUS Test GPC AA 1357 Australia RR AUS Test RR 3 Australia AA Pink Car AA 99 Australia AA Pink Car AA 99 Australia ZZ AUS Test BB / ZZ 456 India ZZ India ZZ Cat 3 India ZZ India ZZ Mouse 9 India AA India Calcium AA 200 I've created a Sheet that has a table with the criteria, waiting for the totals to be entered: Australia AA Pink Car AA (formula). I've tried playing around with SUMIF, and SUBTOTAL, tried nesting a combination of IF and AND statements, but it's becoming apparent to me that I have no idea of what I'm doing. Any help would be much appreciated. Thanks. - Cristian. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Subtotalling | Excel Worksheet Functions | |||
match multiple criteria ina range from multiple criteria multiplet | Excel Worksheet Functions | |||
Subtotalling | Excel Worksheet Functions | |||
Counting Cells with multiple criteria.One criteria supporting wild | Excel Worksheet Functions | |||
Counting Cells with multiple criteria.One criteria supporting wild | Excel Worksheet Functions |