Need formula to count number of times "Hard Drive" appears

Posted by Tony on January 21, 2002 11:11 AM

I have a purchasing spreadsheet where i keep track of individual purchases and the amount of money i spent for each item (eg:Hard Drive, 3 purchases, $150.00 ea.) On sheet 2, i would like to have a column that will tell me how many times the word "Hard Drive" appears on sheet 1. This way, when i have a monthly total appearing on sheet 2 (eg:$450.00), i will also know how many purchases are contained within that $450.00 total. The answer will appear in cell H21 on sheet 2 in numeral form.

Any ideas?

Thanks,
Tony

Posted by bj on January 21, 2002 11:26 AM

trying using
countif(sheet1!$col$row:$col$row,"*Hard Drive*")

where is says [sheet1!] add range that you want to search. you can also substitute the "hard drive" text with a cell reference so you can copy the formula down and have it count whatever you're adding up in that row.

Posted by denis on January 21, 2002 11:32 AM

use this formula =COUNTIF(Sheet1!A3:A5,"hard drive")

=COUNTIF(Sheet1!A3:A5,"hard drive")

Posted by Aladin Akyurek on January 21, 2002 11:32 AM

> you can also substitute the "hard drive" text with a cell reference

Just as a reminder: With the proviso that the cell to be referred to houses *Hard Drive*, not just Hard Drive. Otherwise, you need concat'ing in the condition of COUNTIF.

Aladin

=========

Posted by Tony on January 22, 2002 11:27 AM

Thanks Guy's!!