Formula to search for data

[gemmavine]

Hi all,

I have a spreadsheet and in one of the columns i have to put if an item is in stock or not and i have to calculate the ones not in stock.

The majority are in stock, and say 'in stock' however, some are out of stock. usually i just manually look up and down the column and count how many 'out of stocks' i can find.
Can someone tell me a formula which will calculate automatically how many there are?

Also, i have to add rows onto the bottom of this when i get orders. How do i make sure excel still calculates all of the 'out of stocks' above, will it auto do this or will i have to change cell references to include all data?

Hope this makes sense.

Regards

Gemma

 

[VoG]

Try like this for column A

=COUNTIF(A:A,"out of stock")

 

[mborg]

Use the COUNTIF function. (=COUNTIF(C1:C11,"in stock"))
Assuming the column you were calculating is in column C, this forumla would return the number of items In stock.

 

[gemmavine]

Hi,

Thanks for your reply however, when using this excel says my formula contains an error.

Regards

Gemma

 

[VoG]

The formula cannot go in column A or you'll get a circular reference

Excel Workbook
	A	B
1	out of stock	3
2	in stock
3	out of stock
4	out of stock
Sheet2

 

[kidwispa]

Hi,

Thanks for your reply however, when using this excel says my formula contains an error.

Regards

Gemma

As given above in VoG's post

=COUNTIF(A:A,"out of stock")

This will work - just amend the "A:A" to match the range you are looking to count.

 

[gemmavine]

Thanks, sorted!