| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumproduct - two+ criteria
I have a lookup table of nine 4-digit codes in cells A59:A67. I have two
cells, M9 and O9, which have the letters A and F inputted in them, respectively. I want a formula that looks to the cells A59:A67 and counts only those codes located in a data column ("PB_Extract" Column E). But I also want the formula to to further filter the results by giving me only account names (located in "PB_Extract" Column E) which has a starting letter within a specific alphabetical range (within the two cells M9 and O9). Can somebody help?! I would very grateful. Please let me know if I need to provide more details. |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sumproduct - two+ criteria
Hello Bryce,
I presume your codes and names can't both be in column E, I'll assume that the names are in column F. I also assume that you want to include all names beginning with A, B, C, D, E and F, if A and F appear in M9 and O9. Try this formula =SUMPRODUCT(--ISNUMBER(MATCH('PB_Extract'!E1:E1000,A59:A67,0)),--(UPPER(LEFT('PB_Extract'!F1:F1000))=M9),--(UPPER(LEFT('PB_Extract'!F1:F1000))<=O9)) adjust ranges as necessary "Bryce" wrote: I have a lookup table of nine 4-digit codes in cells A59:A67. I have two cells, M9 and O9, which have the letters A and F inputted in them, respectively. I want a formula that looks to the cells A59:A67 and counts only those codes located in a data column ("PB_Extract" Column E). But I also want the formula to to further filter the results by giving me only account names (located in "PB_Extract" Column E) which has a starting letter within a specific alphabetical range (within the two cells M9 and O9). Can somebody help?! I would very grateful. Please let me know if I need to provide more details. |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sumproduct - two+ criteria
This is awesome! Thank you so much. It works perfectly!
"daddylonglegs" wrote: Hello Bryce, I presume your codes and names can't both be in column E, I'll assume that the names are in column F. I also assume that you want to include all names beginning with A, B, C, D, E and F, if A and F appear in M9 and O9. Try this formula =SUMPRODUCT(--ISNUMBER(MATCH('PB_Extract'!E1:E1000,A59:A67,0)),--(UPPER(LEFT('PB_Extract'!F1:F1000))=M9),--(UPPER(LEFT('PB_Extract'!F1:F1000))<=O9)) adjust ranges as necessary "Bryce" wrote: I have a lookup table of nine 4-digit codes in cells A59:A67. I have two cells, M9 and O9, which have the letters A and F inputted in them, respectively. I want a formula that looks to the cells A59:A67 and counts only those codes located in a data column ("PB_Extract" Column E). But I also want the formula to to further filter the results by giving me only account names (located in "PB_Extract" Column E) which has a starting letter within a specific alphabetical range (within the two cells M9 and O9). Can somebody help?! I would very grateful. Please let me know if I need to provide more details. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| sumproduct formula (multiple criteria) | Excel Discussion (Misc queries) | |||
| Sumproduct using named ranges and multiple criteria | Excel Discussion (Misc queries) | |||
| SUMPRODUCT Criteria Via Cell Reference?? | Excel Worksheet Functions | |||
| Multiple Criteria in SumProduct, N/A Result | Excel Worksheet Functions | |||
| SUMPRODUCT Formula to Count Row of data Below Matched Criteria | Excel Worksheet Functions | |||
Hybrid Mode
