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

	A	B	C
1	Tire	Qty
2	Tire A	2
3	Tire B	2
4	Shortage:	0	1
5	Tire Type	Week 1	Week 2
6	Tire A	2	1
7	Tire B	0	2

<tbody>
</tbody>

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,"")