Excel : count occurences found in another range matching criteria

Question

I'm pretty sure a combinaison of COUNTIFS, SUMPRODUCT, VLOOKUP, INDEX or MATCH would do the trick, but really I can't get in Excel logic and not able to do it. The closest matches I found are this and this, but I was not able to transpose it. Help much appreciated.

Here is a sample of problem with expected output in yellow. So for instance, E2 is the number of value greater than 1 we found in range B2:B7 for which product is equal to A.

Sorry F3 is wrong, it should be 2 (we have 2 value of B product greater than 2)

Well I able to do use =COUNTIF(B2:B7;">=1") but how to add the lookup between product ? — xtruder99, Jun 06 '19 at 15:02
pfff it make me sad to not having found it by myself and being so bad but yes this is working perfectly. Thank you very much!!! — xtruder99, Jun 06 '19 at 15:09

score 0 · Answer 1 · answered Jun 06 '19 at 15:10

0

Thanks to BigBen:

Formula to use and transpose: =COUNTIFS($A$2:$A$7;$D2;$B$2:$B$7;">1")

answered Jun 06 '19 at 15:10

xtruder99

61
5

Excel : count occurences found in another range matching criteria

1 Answers1