trying to find a countifs fomula its coming up as "0" when should be "2"

Question

trying to find a countifs fomula its coming up as "0" when should be "2"

Lee Pakeman 20

Screenshot (1)

Lee Pakeman 20 Reputation points

2026-02-07T15:17:55.8133333+00:00

If you change formula To H=1 A=2 then countifs should be 1
Marcin Policht 81,795 Reputation points MVP Volunteer Moderator

2026-02-07T18:01:34.9266667+00:00
If you replace H with 1 and A with 2 in your logic, then your COUNTIFS or SUMPRODUCT formula needs to check for 1 or 2 instead of "H" or "A". For example, using SUMPRODUCT you could write:

=SUMPRODUCT((E4:GZ4=0)*ISNUMBER(MATCH(E3:GZ3,{1,2},0)))

This will count columns where row 4 is 0 and row 3 is either 1 or 2. If you prefer COUNTIFS, you’d need to add two separate COUNTIFS together:

=COUNTIFS(E3:GZ3,1,E4:GZ4,0)+COUNTIFS(E3:GZ3,2,E4:GZ4,0)

Make sure that the cells in row 3 actually contain numbers 1 and 2, not text "1" or "2", otherwise Excel won’t match them. You can test a cell with

=ISNUMBER(E3)

to confirm. If it returns FALSE, then you need to compare against "1" and "2" as text instead.

If the above response helps answer your question, remember to "Accept Answer" so that others in the community facing similar issues can easily find the solution. Your contribution is highly appreciated.

hth

Marcin
Ashish Mathur 101.7K Reputation points Volunteer Moderator

2026-02-08T00:04:31.76+00:00

Hi,

Please share the download link of the MS Excel file.

Answer accepted by question author

1 additional answer

Your answer

Lee Pakeman 20 Reputation points

2026-02-07T15:17:55.8133333+00:00

If you change formula To H=1 A=2 then countifs should be 1
Marcin Policht 81,795 Reputation points MVP Volunteer Moderator

2026-02-07T18:01:34.9266667+00:00

If you replace H with 1 and A with 2 in your logic, then your COUNTIFS or SUMPRODUCT formula needs to check for 1 or 2 instead of "H" or "A". For example, using SUMPRODUCT you could write:

=SUMPRODUCT((E4:GZ4=0)*ISNUMBER(MATCH(E3:GZ3,{1,2},0)))

This will count columns where row 4 is 0 and row 3 is either 1 or 2. If you prefer COUNTIFS, you’d need to add two separate COUNTIFS together:

=COUNTIFS(E3:GZ3,1,E4:GZ4,0)+COUNTIFS(E3:GZ3,2,E4:GZ4,0)

Make sure that the cells in row 3 actually contain numbers 1 and 2, not text "1" or "2", otherwise Excel won’t match them. You can test a cell with

=ISNUMBER(E3)

to confirm. If it returns FALSE, then you need to compare against "1" and "2" as text instead.

If the above response helps answer your question, remember to "Accept Answer" so that others in the community facing similar issues can easily find the solution. Your contribution is highly appreciated.

hth

Marcin
Ashish Mathur 101.7K Reputation points Volunteer Moderator

2026-02-08T00:04:31.76+00:00

Hi,

Please share the download link of the MS Excel file.

Answer 1

IlirU 1,936 Volunteer Moderator

User's image

Hi,

See the screenshot above and apply formula in cell HD2:

=BYCOL(HD1:HS1, LAMBDA(a, SUM(--(FILTER(GI2:GZ2, GI1:GZ1 = "H") & "-" & FILTER(GI2:GZ2, GI1:GZ1 = "A") = a))))

Change the range in formula as per you need.

Hope this helps.

IlirU

Lee Pakeman 20 Reputation points

2026-02-10T19:41:58.85+00:00

Wonderful just what I required THANK YOU
IlirU 1,936 Reputation points Volunteer Moderator

2026-02-11T16:51:31.39+00:00

You are welcome.

Answer 2

Marcin Policht 81,795 MVP Volunteer Moderator

It looks like your formula returns 0 because COUNTIFS applies AND logic to every condition, so you are asking Excel to count cells where the same position is "H" and "A" at the same time, which can never happen.

If you want to count columns where row 3 is H or A and row 4 is 0, use two COUNTIFS and add them together:

=COUNTIFS(E3:GZ3,"H",E4:GZ4,0)+COUNTIFS(E3:GZ3,"A",E4:GZ4,0)

Or a single formula version:

=SUMPRODUCT((E4:GZ4=0)*((E3:GZ3="H")+(E3:GZ3="A")))

If it still returns 0 then your zeros may be stored as text, in which case replace 0 with "0".

If the above response helps answer your question, remember to "Accept Answer" so that others in the community facing similar issues can easily find the solution. Your contribution is highly appreciated.

hth

Marcin

Lee Pakeman 20 Reputation points

2026-02-07T14:16:26.33+00:00

The first formula the answer came out as six not two the second formula was nowhere near came out 130
Lee Pakeman 20 Reputation points

2026-02-07T14:24:47.4+00:00

Thank you
Marcin Policht 81,795 Reputation points MVP Volunteer Moderator

2026-02-07T14:46:19.1966667+00:00
I gather your original formula in the bar is effectively asking Excel to find columns where row 3 is H and A at the same time, which will always give zero. When you tried the fixes and got six or 130 it means Excel is counting more columns than you expected, so either your range is too wide or there are more qualifying 0 values than expected.

Right now you are scanning E3:GZ4 which is hundreds of columns. In your image only a small block near the right actually contains the H/A with 0 combinations. Excel is counting everything in the full range, not just the visible matches you had in mind.

Try using this version which handles the OR condition properly and is safer:

=SUMPRODUCT((E4:GZ4=0)*ISNUMBER(MATCH(E3:GZ3,{"H","A"},0)))

If that still gives a large number then the formula is working and your sheet migh have many more zeros paired with H or A than you realised. To prove it, temporarily shrink the range to only the columns where you expect two matches, for example something like:

=SUMPRODUCT((GG4:GT4=0)*ISNUMBER(MATCH(GG3:GT3,{"H","A"},0)))

If the smaller range returns two then your logic is correct and the earlier big number came from extra qualifying columns.

Also check whether your zeros are true numbers or text. Test one cell with:

=ISNUMBER(GG4)

If it returns FALSE then switch the formula comparison to "0" instead of 0.

If the above response helps answer your question, remember to "Accept Answer" so that others in the community facing similar issues can easily find the solution. Your contribution is highly appreciated.

hth

Marcin

Share via

trying to find a countifs fomula its coming up as "0" when should be "2"

1 additional answer

Your answer