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.

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.

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.