COUNTIFS with distinct values

nburaq

Board Regular
Joined
Apr 2, 2021
Messages
220
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
Hi,
I have a table called "Table1" at Sheet2 and I would like to find the number of events occurs at specified week. I would like to learn how to count of every value in a table based on week number entered and "Planned" or "Maintenance" and as final result counting of every distinct value in a table
sheet1 A1: 8

A B C D E
CategoryReasonWeekEvent DescAccepted
PlannedMechanical8Day StopYes
MaintenanceElectrical8Low Gas PressureYes
PlannedMechanical10Day StopYes
MaintenanceElectrical10Low Gas PressureNo

My approach was in A2: =SUMPRODUCT(1/COUNTIFS(Table1[Event Desc];Table1[@[Event Desc]];Table1[Week];$A1;Table1[Accepted];"Yes";Table1[Category];{"Planned";"Maintenance"}))

Thanks for your help and concern!
 

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).
Power Query Solution using Group By Function

Power Query:
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    CountEvents = Table.Group(Source, {"Week", "Category"}, {{"Count", each Table.RowCount(Table.Distinct(_)), Int64.Type}})
in
    CountEvents
 
Upvote 0
With a formula
Fluff.xlsm
ABCDEFGH
1CategoryReasonWeekEvent DescAccepted
2PlannedMechanical8Day StopYes82
3MaintenanceElectrical8Low Gas PressureYes
4PlannedMechanical10Day StopYes
5MaintenanceElectrical10Low Gas PressureNo
6Planned8Day StopYes
Data
Cell Formulas
RangeFormula
H2H2=ROWS(UNIQUE(FILTER(Table1[Event Desc],(Table1[Week]=G2)*(Table1[Accepted]="yes")*((Table1[Category]="Planned")+(Table1[Category]="Maintenance")))))
 
Upvote 0
Solution
Hi Fluff and Alan,
Sorry for my late reply, I was quite busy nowadays but I confirm that both methods work great! I am so thankful to both of you by solving one of my problem! Thanks again
 
Upvote 0
Glad we could help & thanks for the feedback.
 
Upvote 0

Forum statistics

Threads
1,213,487
Messages
6,113,937
Members
448,534
Latest member
benefuexx

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top