COUNT using 2 different criterias

  • Thread starter Thread starter fsfiligoi
  • Start date Start date
F

fsfiligoi

Hello. I'm trying to count how many days I worked remotly, how many days I
was sick and how many vacation days I took each month.
Month Date Type
Jan 5 Work Remotly
Jan 6 Sick Day
Jan 23 Work Remotly
Feb 11 Sick Day
Mar 16 Work Remotly
Mar 17 Vacation Day
In this example, this would be the result:
Jan Feb Mar Type
2 0 1 Work Remotly
1 1 0 Sick Day
0 0 1 Vacation Day
What would the formula be to get those results? Thanks! Fatima
 
With this data in the range A2:C7
Jan 5 Work Remotly
Jan 6 Sick Day
Jan 23 Work Remotly
Feb 11 Sick Day
Mar 16 Work Remotly
Mar 17 Vacation Day
Click to expand...

F1:I1 = headers = Jan Feb Mar Type

I2:I4 = Work Remotly; Sick Day; Vacation Day

Enter this formula in F2:

=SUMPRODUCT(--($A$2:$A$7=F$1),--($C$2:$C$7=$I2))

Copy across to H2 then down to row 4
 
Hi
The easiest way is with a Pivot Table
Place you cursor anywhere within your data>Data>Pivot Tables>Finish
On the Pivot Table skeleton that appears on the new sheet
Drag Month to the column area
Drag Type to the Row area
Drag Type again to the Data area (where it will show Count of Type)
 
Back
Top