countIF or sumproduct?

[jakelake]

Hello,

I have a table with columns that show how many bike tires I need each week. The rows in the t able are the various types of tires. I am trying to come up with a formula that counts the number of shortages. I have been able to set up conditional formatting to identify the shortages, but I cant get a formula to count.

Qty of Tire A: 2
Qty of Tire B: 2


[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]Tire[/TD]
[TD="align: center"]Qty
[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]Tire A[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"]Tire B[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: center"]Shortage:
[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]1[/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: center"]Tire Type[/TD]
[TD="align: center"]Week 1[/TD]
[TD="align: center"]Week 2[/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD="align: center"]Tire A
[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]1[/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD="align: center"]Tire B[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]2[/TD]
[/TR]
</tbody>[/TABLE]

I am trying to find a way to have Row 1 calculate the shortages. As I said, with conditional formatting I can do the formula =SUM($B6:B6)>=$B2

Conditional formatting will automatically change the row and column based on the cells the formatting is being applied too. How do i do this with a formula? I assume I need to use an array or sumproduct. However, I am struggling to think it through or get it to work.

Any ideas?

 

[etaf]

row 1 to show what exactly - total shortage of each week

in B6 and B7 - should =4
in C6 and C7 - should =4
etc
what do you need in row
you could do
=(B6+B7)-4 will give you the shortage
or do you want to see the types of tyre that are short and where would those results go ?

=(B6+B7)>=(B2+B3)

 

[r1998]

Hello
use this formula in C6 and drag down till C7

=IF(B6>=B2,B6-B2,"")