Excel rows count using counting formula involving multiple columns

Question

I'm struggling to take a count of rows in excel sheet (using formula) involving 3 columns of data say a, b & c. The condition is having value 111 in column a besides column b or column c having a 100. So, it is like

a2:a500="111" AND (b2:b500=100 OR c2:c500=100)

Possible duplicate of [Count if two criteria match - EXCEL formula](https://stackoverflow.com/questions/10158675/count-if-two-criteria-match-excel-formula) — Tripp Kinetics, Mar 30 '18 at 18:21

Tripp Kinetics · Accepted Answer · 2018-03-30T18:33:51.950

0

How about:

=COUNTIFS(A2:A500, "111", B2:B500, "100") + COUNTIFS(A2:A500, "111", C2:C500, "100", B2:B500, "<>100")

edited Mar 30 '18 at 18:33

answered Mar 30 '18 at 18:20

Tripp Kinetics

5,178
2
23
37

1

This won't work because if for example A2 = 111, B2 = 100, and C2 = 100, your formula will erroneously count that entry twice. You can modify the second `COUNTIFS` to account for this, i.e. `COUNTIFS(A2:A500, "111", C2:C500, "100",B2:B500,"<>100")` – ImaginaryHuman072889 Mar 30 '18 at 18:30
@SwapanGhosh Does that fix it? – Tripp Kinetics Mar 30 '18 at 18:35
@Tripp Kinetics yes, that does...thnx – Swapan Ghosh Mar 30 '18 at 18:50
@TrippKinetics How do I upvote and accept...pls explain. – Swapan Ghosh Mar 30 '18 at 19:01
Click the grey checkbox next to my answer to accept. Click the up arrow next to my answer to upvote. – Tripp Kinetics Mar 30 '18 at 19:02

Scott Craner · Answer 2 · 2018-03-30T21:29:32.010

0

You can use SUMPRODUCT:

=SUMPRODUCT((a2:a500=111)*((b2:b500=100)+ (c2:c500=100)>0))

edited Mar 30 '18 at 21:29

answered Mar 30 '18 at 18:47

Scott Craner

148,073
10
49
81

It does have the same problem of double counting as mentioned by imagineryHuman072889 – Swapan Ghosh Mar 30 '18 at 18:59
1

@SwapanGhosh To fix, do this: `=SUMPRODUCT((a2:a500=111)*((b2:b500=100)+ (c2:c500=100))>0)` – ImaginaryHuman072889 Mar 30 '18 at 20:59

Excel rows count using counting formula involving multiple columns

2 Answers2