MrExcel Publishing
Your One Stop for Excel Tips & Solutions

count if

Posted by Sydney on July 23, 2001 8:32 AM

I have a spreadsheet showing the number of widgits produced by a certain technician in a date range of July 2000 to June 2001. I want to know how many were processed per month by that technician and how many days worked per month. It seems like the COUNTIF would be perfect but is it for text only?

Posted by Aladin Akyurek on July 23, 2001 10:12 AM


How are your dates entered? You could provide 20 rows of data that you have. I inclined to do so, use the following procedure:

Activate an empty cell, type =, select 20 rows of your data range, and hit the combination control+shift+enter at the same time. Go to the formula bar, select the formula, hit F9, copy the array that you see, and post it in the follow-up. (Dates will show up as ordinary numbers, but that's OK.)



Posted by Sydney on July 23, 2001 11:14 AM

Posted by Aladin Akyurek on July 23, 2001 11:36 AM


You did it right. But can you do it again including also the columns in which you see technicians (if confidential, just replace names by letters), widget processed at each date? I presume that if a technician did not work a given day, the corresponding cell in widget column is empty, right?


========== for your Help!

Posted by Sydney on July 23, 2001 11:56 AM

I just tried to copy the array as before but it gives me #VALUE!. The technician is BD. On each row of the spreadsheet I have the widgit number (unique to each widgit), the date it was processed, techs initals, the weight of the widget. I want to get data on the tech, how many per month is she processing, how many on a given work day and/or how many days/mo did she work (based on what dates are present). So for the array I sent in the previous followup, in the column that is next to the dates are just the initals BD. Does this help? I don't know what I am doing wrong when I try to select both colums. Am I making any sense?
Sydney for your Help!

Posted by Aladin Akyurek on July 24, 2001 7:21 AM


I have the impression that you'd be better off using PivotTables. With that hunch in mind, I'm very reluctant to stuff your spreadsheets with loads of formulas. I believe Barrie Davidson got an example at his web site, which shows how to set up a pivot table. Don't remember his URL, but do a find/search on this page (or on Archive2) using e.g., his name as keyword.