CountIF with array

[jakelake]

Hello,

I have a table with store names in a row and store items in a column. Based on the quantity of items on hand, I am trying to see how many stores can be serviced. The meat of the table has the quantity each store needs for each item.

I am trying to come up with a count-if formula that will count the store shortages. I have been able to have conditional formatting highlight the shortages automatically. However, I can't figure out how to count the red cells.

it won't let me attach a workbook, so i pasted my table below. I assume I have to do an single-cell array, but I am completely lost. The purple numbers are what I want the formulas to output. Any ideas?

Thank you for your time

[TABLE="width: 512"]
<colgroup><col width="64" span="8" style="width:48pt"> </colgroup><tbody>[TR]
[TD="class: xl65, width: 64, align: center"] [/TD]
[TD="class: xl65, width: 64, align: center"]A[/TD]
[TD="class: xl65, width: 64, align: center"]B[/TD]
[TD="class: xl65, width: 64, align: center"]C[/TD]
[TD="class: xl65, width: 64, align: center"]D[/TD]
[TD="class: xl65, width: 64, align: center"]E[/TD]
[TD="class: xl65, width: 64, align: center"]F[/TD]
[TD="class: xl65, width: 64, align: center"]G[/TD]
[/TR]
[TR]
[TD="class: xl65, align: center"]1[/TD]
[TD="class: xl66, align: center"] [/TD]
[TD="class: xl66, align: center"]Item Qty[/TD]
[TD="class: xl66, align: center"]Store 1[/TD]
[TD="class: xl66, align: center"]Store 2[/TD]
[TD="class: xl66, align: center"]Store 3[/TD]
[TD="class: xl66, align: center"]Store 4[/TD]
[TD="class: xl66, align: center"]Store 5[/TD]
[/TR]
[TR]
[TD="class: xl65, align: center"]2[/TD]
[TD="class: xl66, align: center"] [/TD]
[TD="class: xl66, align: center"] [/TD]
[TD="class: xl66, align: center"] [/TD]
[TD="class: xl66, align: center"] [/TD]
[TD="class: xl66, align: center"] [/TD]
[TD="class: xl66, align: center"] [/TD]
[TD="class: xl66, align: center"] [/TD]
[/TR]
[TR]
[TD="class: xl65, align: center"]3[/TD]
[TD="class: xl66, align: center"] [/TD]
[TD="class: xl66, align: center"] [/TD]
[TD="class: xl68, align: center"]1[/TD]
[TD="class: xl68, align: center"]2[/TD]
[TD="class: xl68, align: center"]2[/TD]
[TD="class: xl68, align: center"]4[/TD]
[TD="class: xl68, align: center"]4[/TD]
[/TR]
[TR]
[TD="class: xl65, align: center"]4[/TD]
[TD="class: xl66, align: center"]Item 1 [/TD]
[TD="class: xl66, align: center"]4[/TD]
[TD="class: xl67, align: center"]1[/TD]
[TD="class: xl67, align: center"]1[/TD]
[TD="class: xl67, align: center"]1[/TD]
[TD="class: xl67, align: center"]1[/TD]
[TD="class: xl67, align: center"]1[/TD]
[/TR]
[TR]
[TD="class: xl65, align: center"]5[/TD]
[TD="class: xl66, align: center"]Item 2[/TD]
[TD="class: xl66, align: center"]4[/TD]
[TD="class: xl67, align: center"]1[/TD]
[TD="class: xl67, align: center"]1[/TD]
[TD="class: xl67, align: center"]1[/TD]
[TD="class: xl67, align: center"]1[/TD]
[TD="class: xl67, align: center"]1[/TD]
[/TR]
[TR]
[TD="class: xl65, align: center"]6[/TD]
[TD="class: xl66, align: center"]Item 3[/TD]
[TD="class: xl66, align: center"]1[/TD]
[TD="class: xl67, align: center"]1[/TD]
[TD="class: xl67, align: center"]1[/TD]
[TD="class: xl67, align: center"]1[/TD]
[TD="class: xl67, align: center"]1[/TD]
[TD="class: xl67, align: center"]1[/TD]
[/TR]
[TR]
[TD="class: xl65, align: center"]7[/TD]
[TD="class: xl66, align: center"]Item 4[/TD]
[TD="class: xl66, align: center"]0[/TD]
[TD="class: xl67, align: center"]1[/TD]
[TD="class: xl67, align: center"]1[/TD]
[TD="class: xl67, align: center"]1[/TD]
[TD="class: xl67, align: center"]1[/TD]
[TD="class: xl67, align: center"]1[/TD]
[/TR]
</tbody>[/TABLE]