Equivalent of COUNTIF and SUMPRODUCT as a Measure

nburaq

Board Regular
Joined
Apr 2, 2021
Messages
222
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
Hi ,
I am trying to create equivalent of Countif and Sumproduct as a "Measure" in Excel If it is possible. I have a table called Table1 and End Result will be displayed at "Acceptance" column as "Yes" or "No" based on the condition specified below;
If Occurence is >=4 or Total Lost Hour is >=8

EventLost TimeOccurrenceTotal Lost HourAcceptance
A10,110,1No
A20,2512Yes
A30,310,3No
B10,410,4No
B20,510,5No
B30,610,6No
C50,722,9No
C70,810,8No
C80,910,9No
A21512Yes
D11,111,1No
D21,223,5No
D31,323,8No
E41,423,8No
E51,511,5No
E61,611,6No
A21,7512Yes
F71,828,8Yes
F7728,8Yes
F9212No
A22,1512Yes
C52,222,9No
D22,323,5No
E42,423,8No
D32,523,8No
F42,612,6No
A23512Yes
A24512Yes

For Occurence : =COUNTIF($A$2:$A$28;$A2)
For Total Lost Hour: =SUMPRODUCT(--($A$2:$A$29=$A2);$B$2:$B$29)
For Acceptance:=IF(OR([@Occurrence]>=4;[@[Total Lost Hour]]>=8);"Yes";"No")

Thanks for your help and comments!
 

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

Forum statistics

Threads
1,224,822
Messages
6,181,165
Members
453,021
Latest member
Justyna P

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