CODEWITHSUNDEEP
excel
Coding Novice
Coding Novice

Reputation: 447

Counting Cells Formula with Criteria: Date & String & Empty Cells

I'm trying to do a whole lot of counting using a table (growing) with over 4,000 entries by date. The table in the picture below is the one I am trying to fill in. I Have a few simple criteria, but I'm not sure how to do this in a formula within a cell that I can copy across this giant table. I've tried multiple CountIf and CountIfs with no luck.

The criteria are as follows: I want to count the number of times a tool occurs (column E) in a particular date (Column A). Only counting when Column W is not empty. Then I want to copy that through this entire table checking every tool by every date.

enter image description here

The data is coming from this table:

enter image description here enter image description here

Upvotes: 1

Views: 174

Answers (1)

Scott Craner
Scott Craner

Reputation: 152450

COUNTIFS(), With your date lookups including a time portion we need to bracket the days.

=COUNTIFS('Sheet1'!$A:$A,">=" & INT($C3),'Sheet1'!$A:$A,"<" & INT($C3)+1,'Sheet1'!$E:$E,D$1,'Sheet1'!$W:$W,"<>")

Change Sheet1 to the sheet name in which the data is located.

Put this in D2 and copy over and down the grid.

Upvotes: 5

Related Questions