When G matches B, count the number of values in C (that do not match H)

Hello,

Column H is a list of six-digit MPNs to be blocked, and Column G shows the component that the MPN relates to. So, within the list of components (columns A:E), I need to count the number of MPNs each Component has...but not including the ones to be blocked (column H). So, if there are 4 different MPN numbers; I want to show that in column J. Then, in column K, I want to show how many of those MPNs have the location of "MTY."

So, I'm just trying to find a formula for columns J and K....

I provided an example in the picture below:
H2 is the first MPN in the list. So, G2 must match B:B (I highlighted the matching component in red). So, I want to count the related MPNs in column C...but excluding the ones where C matches H (highlighted in yellow). You can see I counted the different MPNs from 1-to-4 in blue (I skipped the one in yelow) and I listed that final count in column J. Likewise, I counted column E, where the location was "MTY" and listed the final count in Column K.
[Blocked Image: http://i26.photobucket.com/albums/c117/shocktrooper327/Excel%20Example_zpso4wlcouf.png]

forum.ozgrid.com/index.php?attachment/72217/

Re: When G matches B, count the number of values in C (that do not match H)

edit

Re: When G matches B, count the number of values in C (that do not match H)

Quote from NBVC;790743

Try these formulas copied down

=SUMPRODUCT(($B$2:$B$324=G2)*($C$2:$C$324<>H2)/COUNTIFS($B$2:$B$324,$B$2:$B$324&"",$C$2:$C$324,$C$2:$C$324&""))

=SUMPRODUCT(($B$2:$B$324=G2)*($C$2:$C$324<>H2)*($E$2:$E$324="MTY")/COUNTIFS($B$2:$B$324,$B$2:$B$324&"",$C$2:$C$324,$C$2:$C$324&"",$E$2:$E$324,$E$2:$E$324&""))

Yes! It's working.

But I notice one flaw in my own description of the problem. I also want to specify that the MPN Status in column D should be either "RL" or "SR" as well, because an MPN shouldn't count if it's already blocked. Likewise if multiple MPNs are linked to the same component (like G2 and G3) then both need to be excluded from the final count in columns J and K...

I think I know how to do it, too...

=SUMPRODUCT(($B$2:$B$324=G2)*($C$2:$C$324<>H2)*($D$2:$D$324="RL")/COUNTIFS($B$2:$B$324,$B$2:$B$324&"",$C$2:$C$324,$C$2:$C$324&"",$D$2:$D$324,$D$2:$D$324&""))

But how can I include "SR" in the formula, too?

Re: When G matches B, count the number of values in C (that do not match H)

Yes, Thank you!