Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sum If on multiple columns with range of criteria
I'm sure this isn't hard. Brain won't cooperate today. Going home. Had
enough. Column A is text (Location code) Column B is numeric (part number) Column C is numeric (qty) How do I sum Col C for all items in Column A that match one criterium (say letter "A") and Column B match a range of criteria (Say p/n 123, 858, 797 and 485). DSUM works great if I had only one set of criteria and could put them next to each other but the report layout requires me keep the criteria section in a limited number of columns. So, I want LOC code = "A" for all the categories of part numbers. I need a sum of qty for each category. I have 8 columns available and 6 different criteria: col1 col2 col3 col4 col5 col6 col7 col8 LOC PN PN PN PN PN PN A 123 334 556 778 850 990 157 482 661 790 889 995 192 498 698 898 275 699 298 I don't have room to put a LOC criteria code next to each PN criteria list. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sum If on multiple columns with range of criteria
Try this...
Use cells to hold your criteria... G2 = some location code H2:H5 = lookup part numbers = 123, 858, 797, 485 =SUMPRODUCT(--(A2:A15=G2),--(ISNUMBER(MATCH(B2:B15,H2:H5,0))),C2:C15) -- Biff Microsoft Excel MVP "44judester" wrote in message ... I'm sure this isn't hard. Brain won't cooperate today. Going home. Had enough. Column A is text (Location code) Column B is numeric (part number) Column C is numeric (qty) How do I sum Col C for all items in Column A that match one criterium (say letter "A") and Column B match a range of criteria (Say p/n 123, 858, 797 and 485). DSUM works great if I had only one set of criteria and could put them next to each other but the report layout requires me keep the criteria section in a limited number of columns. So, I want LOC code = "A" for all the categories of part numbers. I need a sum of qty for each category. I have 8 columns available and 6 different criteria: col1 col2 col3 col4 col5 col6 col7 col8 LOC PN PN PN PN PN PN A 123 334 556 778 850 990 157 482 661 790 889 995 192 498 698 898 275 699 298 I don't have room to put a LOC criteria code next to each PN criteria list. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Filter Multiple Columns on Multiple Criteria and count the last co | Excel Worksheet Functions | |||
To count the data using multiple criteria in multiple columns | New Users to Excel | |||
create new table from Multiple Criteria in multiple columns | Excel Worksheet Functions | |||
match multiple criteria ina range from multiple criteria multiplet | Excel Worksheet Functions | |||
Counting from one range to another range, multiple criteria | Excel Discussion (Misc queries) |