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. |
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. |
All times are GMT +1. The time now is 07:49 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com