Excel: Can't get =COUNTIFS to Count all Cells Containing a Value

Question

Excel: Can't get =COUNTIFS to Count all Cells Containing a Value

Andy Bebbington 20

Hi,

I'm trying to use the COUNTIFS to count the number of times a particular value occurs in a number of non-contiguous cells in a line. This is the syntax which I'm using:-

=COUNTIFS($B$6,"=5",$N$6,"=5",$T$6,"=5")

Rather than count the number of occurrences, it is returning a 1 if all 3 cells contain the value (5 in this case) and 0 if any of the cells don't contain the value. I would expect it to return 3 (all 3 cells contain the value), 2 (any 2 out of 3 cells contain the value) or 1 (any one out of the 3 cells contain the value).

Ultimately, I want to increase the number of cells which this function looks at and counts up how many times a value occurs. Thus, for line 6, it will look at B6, D6, F6, H6, J6, L6, N6, P6, R6, T6, V6 and X6 and tell me how many times the number 5 occurs.

Where am I going wrong?

Thanks

Andy B

0 comments

Answer accepted by question author

2 additional answers

Your answer

Answer 1

EmilyS726 227.5K Independent Advisor

COUNTIFS is doing exactly what it is designed to do: it counts rows where ALL criteria pairs are true at the same time. It cannot return 2 or 3 because COUNTIFS does not add matches across separate cells, it evaluates them together as a single record.

You might want to use this instead

=COUNTIF(CHOOSE({1,2,3},B6,N6,T6),5)

Andy Bebbington 20 Reputation points

2026-01-29T14:10:33.75+00:00
Hi Emily, Thanks for your quick response and answer. It appears that COUNTIFS doesn't do which I had thought it did from my reading of various Help topics.

I've tried your suggestion but I can't get it to work. Some points/questions about your proposed solution:-

I don't understand what {1,2,3} is doing.

Should I be replacing 1,2,3 in { } with something else?

If I your solution, I get an overspill into the two columns to the right of where this formula is (AA6) and, #VALUE! IN AA6, AB6 and AC6 FIELDS.

If I take {1,2,3} out of your formula, (thus, inserting in AA6:-=COUNTIF(CHOOSE($B$6,$N$6,$T$6),5)

I get #VALUE! in AA6.

Clearly, I still not doing something right? Any further suggestions?

Thanks

Andy B
EmilyS726 227.5K Reputation points Independent Advisor

2026-01-29T14:16:44.47+00:00

Can you try this one instead.. I think we need to use the SUMPRODUCT() to help us.

=SUMPRODUCT(--(B6:X6=5),--(MOD(COLUMN(B6:X6)-COLUMN(B6),2)=0))

I tested, and it works for me.
Andy Bebbington 20 Reputation points

2026-01-29T14:43:10.27+00:00

Hi Emily,

Thanks again. That's better but we're not there yet. Part of the problem is that I understand a bit when it comes to formulas but not enough - I have no formal training in writing code, just what I have picked up.

I only included 3 sample columns in my example as I wanted to start with something simpler than the final version so that I could walk before I try to run.

I think that what you have done is to first look at all the columns B6:X6 (the part, =SUMPRODUCT(--(B6:X6=5), of your solution) then got it to leave out some of the columns (the remainder of your solution). Have I guessed correctly?

What you have sent works for the 3 columns in question. What I actually want it to do is look at every other column between B6 and X6. i.e. B6, D6, F6 ... X6. I'm trying to see how yours works and how I might modify it to achieve my final goal.

After that, I'm hoping to put a similar formula in cell AB6 which counts up the occurrences of number 3 in the same cells. Then similar counting occurrences of number 1 in those cells.

After that, I'm hoping to replicate it onto lines 7 to 20 down my spreadsheet.

If you can explain how your formula works, I could have a go myself so that I learn something rather than expecting you to do all the work.

Thanks for your help.

Andy B
EmilyS726 227.5K Reputation points Independent Advisor

2026-01-29T14:59:45.58+00:00

Yes, that's correct.

I will break it down:

--(B6:X6=5) looks across all the cells from B6 through X6 and marks each one as yes or no basically behind the scene.. yes if it contains 5, no if it doesn’t. So basically B6:X6 defines the entire data range.

--(MOD(COLUMN(B6:X6)-COLUMN(B6),2)=0) then marks which columns you want to include. It keeps every other column starting with B (B, D, F, …, X) and marks the in-between columns (C, E, G, …, W) as “skip.” The number two there indicates the every other column. If you want to count every third column, you change number 2 to 3, so on and so forth.

SUMPRODUCT combines those two checks and totals them up, so you only count cells that are both a “yes, this is 5” and a “yes, this column is one we’re counting.”

I hope that helps explain it a little?
Andy Bebbington 20 Reputation points

2026-01-29T15:42:26.8+00:00

Hi Emily,

Yes! Yes! Yes! Excellent - thank you.

Not only has this resolved my problem but I have learnt something in the process.

All working now.

Best wishes

Andy B
EmilyS726 227.5K Reputation points Independent Advisor

2026-01-29T15:45:31.7866667+00:00

Oh good. I am very glad to hear that :)

Answer 2

Ashish Mathur 101.8K Volunteer Moderator

Hi,

Try this formula

=SUM(1*(CHOOSECOLS(B6:F6,SEQUENCE(,ROUNDUP(COLUMNS(B6:F6)/2,0),,2))=5))

Hope this helps.

User's image

Andy Bebbington 20 Reputation points

2026-01-31T16:43:27.4966667+00:00

Hi Ashish,

I already had an answer from another advisor and it worked so I haven't tried your solution.

Thank you for your interest and response.

Andy Bebbington

Answer 3

Andy Bebbington 20

Hi Emily,

Following your latest response, I tried clicking on See The Answer so that I could click on Accept but it comes up with a 404 error, not found.

I see an Accept Answer against your first response but haven't clicked it in case this only accepts the first response rather than the whole thread. Thus, before I click there can you advise if it is correct to click that one or should there be another Accept Answer nearer to the bottom of this thread?

Thanks

EmilyS726 227.5K Reputation points Independent Advisor

2026-01-29T16:00:07.9766667+00:00

I only posted one answer, the rest were comments.. so I do believe you just need to mark that one. Honestly, if it still gives you a hard time, no worries. I appreciate it though.

Share via

Excel: Can't get =COUNTIFS to Count all Cells Containing a Value

2 additional answers

Your answer