Hi everyone, I'm attempting to count the frequency of events which I managed to get set up. I was rather pleased with it until I realised not everyone works every day. To track this accurately I'm now trying to count the frequency of IF (this) OR (that).
In my example I am counting the frequency of "S" events, but I would like it to count the frequency of "S" and "X" events (combined - if this is possible?).
S S S Blank S S S would be 2 events of "S" and 6 entries of "S"
S X S Blank S X S should be 2 events of "S OR X" and 4 entries of "S", 2 entries of "X". - I can do the COUNTIF for the entries, but I'm having a lot of problems with counting the frequency of events.
S X S Blank S X S - with my current formula this counts 4 events of "S"; but I would like this to be counted 2 events of "S OR X".
I have attached a sample worksheet that I hope makes more sense!
Current Frequency (array) formula:
{=SUM(IF(FREQUENCY(IF(B3:J3="S",COLUMN(B3:J3)),IF(B3:J3<>"S",COLUMN(B3:J3)))>0,1))} (This counts the frequency of "S" events over B3 - J3)
Failed Frequency IF OR Formula attempt:
{=SUM(IF(FREQUENCY(IF(OR(B3:J3="S",B3:J3="X"),COLUMN(B3:J3)),IF(OR(B3:J3<>"S",B3:J3<>"X"),COLUMN(B3:3)))>0,1))}
Failed Array inside an Array Formula attempt:
{=SUM(IF(FREQUENCY(IF(B3:J3={"S","X"},COLUMN(B3:J3)),IF(B3:J3<>{"S","X"},COLUMN(B3:J3)))>0,1))}
Thanks in advance for any ideas, suggestions and solutions!
Excel_Noob122
Bookmarks