Counting in multiple column

  • Thread starter Thread starter Hope
  • Start date Start date
H

Hope

I am struggling with a spreadsheet attempting to count events with criteria
in several columns.

Column A - MMM-YY date (only month and year are keyed in)
Column B - Reference Number (with blanks that have to be omitted from count)
Column C - Equipment Description (contains concantenation of 3 other columns)

I need to count only those lines where C meets one criterion, B is not blank
and A is in the correct month. I hope to put each in a X:X range so the
formulas will not have to be revised as new data is added. I tried
SUMPRODUCT but I think my cell formatting may not be correct to achieve this.
I also put in a cell range A1:A30 which did not solve the problem. I
struggle with picking up the dates appropriately as well. The output I am
seeking is:

Count of only those cells where Column C matches the equipment description
and Column B is not blank for the month required from Column A.

Any help would be greatly appreciated.
 
You prb had trouble getting the month to match.

=SUMPRODUCT((month(A1:A30)=1)*(ISBLANK(B1:B30)=FALSE)*(C1:C30="Truck"))

You could change the 1 to any number up to 12, or reference another cell
such as =MONTH(D1)

"Truck" could of course also be replace with a cell reference. Hope that
helps.
 
Luke,

Thank you so much. I knew I was having trouble getting the logic to match
the date format. Great work-around!!

Hope
 
Back
Top