Count Zero's Across Rows if column A Matches

Magnolia0913

New Member
Joined
Jan 27, 2015
Messages
7
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.

RowsColumns > AHJHKHLHMHNHO
1SKU202005012020050220200503202005042020050520200506
283BW1031113632000000
284BW1031113634100000
285BW1031113636000000
286BW1031113730222222
287BW1031113732444444

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.

RowsColumns > ABCD
1SKUFormulaResultShould Be
283BW1031113632=COUNTIFS(Index!A:A,ZeroVsAvail!A283,Index!HJ:HO,"0")#VALUE!6
284BW1031113634=COUNTIFS(Index!A:A,ZeroVsAvail!A284,Index!HJ:HO,"0")#VALUE!5
285BW1031113636=COUNTIFS(Index!A:A,ZeroVsAvail!A285,Index!HJ:HO,"0")#VALUE!6
286BW1031113730=COUNTIFS(Index!A:A,ZeroVsAvail!A286,Index!HJ:HO,"0")#VALUE!0
287BW1031113732=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.

Thank you kindly in advance.
 

Some videos you may like

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
Joined
Jun 12, 2014
Messages
47,887
Office Version
  1. 365
Platform
  1. Windows
With countifs the ranges must be the same size.
Will the SKU only appear once in the index sheet?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
47,887
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Jan 27, 2015
Messages
7
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
Joined
Jun 12, 2014
Messages
47,887
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,112,787
Messages
5,542,509
Members
410,559
Latest member
jordansmith6532
Top