# Count Zero's Across Rows if column A Matches

#### Magnolia0913

##### New Member
I could really use some assistance in trying to figure out how to count the number of times the value 0 shows across many, many columns, if the value in column A matches between the two sheets. Once I solve this, I hope to reuse the formula further and query how many times it is greater than Zero (0).

Data sheet name is 'Index'.
Column A is the product I'm wanting the counts for, and column 20200501 is column HJ, through 20200506 which is column HO.

 Rows Columns > A HJ HK HL HM HN HO 1 SKU 20200501 20200502 20200503 20200504 20200505 20200506 283 BW1031113632 0 0 0 0 0 0 284 BW1031113634 1 0 0 0 0 0 285 BW1031113636 0 0 0 0 0 0 286 BW1031113730 2 2 2 2 2 2 287 BW1031113732 4 4 4 4 4 4

My results sheet name (in the same file) is 'ZeroVsAvail'.
Column A is the product which needs to be matched from Index file.
Column B is the Formula I'm currently using.
Column C is the result it is giving me.
Column D is the result I'm trying to achieve.
E.g. For BW1031113632, the value 0 appears 6 times between HJ283:HO283. But for BW1031113634, the value 0 only appears 5 times between HJ284:HO284. For the bottom two SKUs, there is not a zero in any cell of the 'Index' cells.

 Rows Columns > A B C D 1 SKU Formula Result Should Be 283 BW1031113632 =COUNTIFS(Index!A:A,ZeroVsAvail!A283,Index!HJ:HO,"0") #VALUE! 6 284 BW1031113634 =COUNTIFS(Index!A:A,ZeroVsAvail!A284,Index!HJ:HO,"0") #VALUE! 5 285 BW1031113636 =COUNTIFS(Index!A:A,ZeroVsAvail!A285,Index!HJ:HO,"0") #VALUE! 6 286 BW1031113730 =COUNTIFS(Index!A:A,ZeroVsAvail!A286,Index!HJ:HO,"0") #VALUE! 0 287 BW1031113732 =COUNTIFS(Index!A:A,ZeroVsAvail!A287,Index!HJ:HO,"0") #VALUE! 0

When I adjust the formula from HJ:HO to just HO:HO, it works perfectly and tell me accurate numbers. But when I try to increase the number of columns to search, it breaks and gives a #VALUE result in the cell.

If COUNTIFS is the wrong formula to use for this, I'm open to any suggestions. I'm not familiar with Pivot Tables at all, so I'm trying to stay away from those so I can use the data going further in my research.

### Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

#### Fluff

##### MrExcel MVP, Moderator
With countifs the ranges must be the same size.
Will the SKU only appear once in the index sheet?

#### Magnolia0913

##### New Member
With countifs the ranges must be the same size.
Will the SKU only appear once in the index sheet?
Yes, the SKU will only appear once in each sheet.

#### Fluff

##### MrExcel MVP, Moderator
In that case try
=COUNTIFS(Index(Index!HJ:HO,match(A283, Index!A:A,0),),"0")
Although it's advisable to limit the number of rows, rather than using whole columns.

#### Magnolia0913

##### New Member
In that case try
=COUNTIFS(Index(Index!HJ:HO,match(A283, Index!A:A,0),),"0")
Although it's advisable to limit the number of rows, rather than using whole columns.
OH MY! Thank you so much! Worked perfectly. Yes, I'm attempting to limit as much as I can, just had to get over this MAJOR hurdle along the way! Again, Thank you so much! You've saved my day!!!

#### Fluff

##### MrExcel MVP, Moderator
You're welcome & thanks for the feedback.

Replies
3
Views
106
Replies
7
Views
83
Replies
11
Views
154
Replies
4
Views
330
Replies
22
Views
229