Adding a second criteria to Sum(IF(Frequency(Offset formula

[ddander54]

I was previously helped with this formula below, but now need to add a second criteria and can't seem to figure out where in the comma/parens it needs to go.

For a unique count of values from Column A, where the corresponding values in Column E equals "Billable", and where the data has been filtered, try...

=SUM(IF(FREQUENCY(IF(SUBTOTAL(3,OFFSET(A29,ROW(A29:A11459)-ROW(A29),0)),IF(E29:E11459="Billable",A29:A11459)),A29:A11459),1))-1

...confirmed with CONTROL+SHIFT+ENTER.

Now I need to add the second criteria IF(F29:F11459=G30,A29:A11459)).

So now I need the unique count of values from Column A, where the corresponding values in Column E = "Billable" and where the corresponding values in Column F equals the value in G30, and where the data has been filtered.

Thanks in advance,
Don

 

[ddander54]

I finally found an answer on StackOverflow...... {=IF(($J$16:$J$22=$L$3)*($K$16:$K$22=$L$4),"MATCH!","-")} Control>Shift>Entered
The key to adding a second condition to an IF in an array formula is to use the * additional condition. * works like AND . + works like OR in an array formula.

 

[Aladin Akyurek]

Control+shift+enter:

=SUM(IF(FREQUENCY(IF(SUBTOTAL(3,OFFSET(A29,ROW(A29:A11459)-ROW(A29),,1)),IF(A29:A11459<>"",IF(E29:E11459="Billable",IF(F29:F11459=G30,MATCH("~"&A29:A11459,A29:A11459&"",0))))),ROW(A29:A11459)-ROW(A29)+1),1))

Does this help?

 

[ddander54]

Aladin,

Yes that works perfectly...Thank you!

I continued to research my question after I first posted it here and found 1 other formula that I got to work using "*" to act as an AND in the formula:
{=SUM(IF(FREQUENCY(IF(SUBTOTAL(3,OFFSET(ProjectFilterTable!$A$29,ROW(ProjectFilterTable!$A$29:$A$11487)-ROW(ProjectFilterTable!$A$29),0)),IF((ProjectFilterTable!$F$29:$F$11487=$F$30)*(ProjectFilterTable!$E$29:$E$11487=$G$29),ProjectFilterTable!$A$29:$A$11487)),ProjectFilterTable!$A$29:$A$11487),1))}

Then your suggestion using a nested IF and Match:
{=SUM(IF(FREQUENCY(IF(SUBTOTAL(3,OFFSET(ProjectFilterTable!$A$29,ROW(ProjectFilterTable!$A$29:$A$11459)-ROW(ProjectFilterTable!$A$29),,1)),IF(ProjectFilterTable!$A$29:$A$11459<>"",IF(ProjectFilterTable!$E$29:$E$11459=$G$29,IF(ProjectFilterTable!$F$29:$F$11459=$F$30,MATCH("~"&ProjectFilterTable!$A$29:$A$11459,ProjectFilterTable!$A$29:$A$11459&"",0))))),ROW(ProjectFilterTable!$A$29:$A$11459)-ROW(ProjectFilterTable!$A$29)+1),1))}

I do have a question tho'....What does the "~" do in the formula? How do I read that in human speak?

Thanks much,
Don

 

[Aladin Akyurek]

Aladin,

Yes that works perfectly...Thank you!

You are welcome.

I continued to research my question after I first posted it here and found 1 other formula that I got to work using "*" to act as an AND in the formula:
{=SUM(IF(FREQUENCY(IF(SUBTOTAL(3,OFFSET(ProjectFilterTable!$A$29,ROW(ProjectFilterTable!$A$29:$A$11487)-ROW(ProjectFilterTable!$A$29),0)),IF((ProjectFilterTable!$F$29:$F$11487=$F$30)*(ProjectFilterTable!$E$29:$E$11487=$G$29),ProjectFilterTable!$A$29:$A$11487)),ProjectFilterTable!$A$29:$A$11487),1))}

Then your suggestion using a nested IF and Match:
{=SUM(IF(FREQUENCY(IF(SUBTOTAL(3,OFFSET(ProjectFilterTable!$A$29,ROW(ProjectFilterTable!$A$29:$A$11459)-ROW(ProjectFilterTable!$A$29),,1)),IF(ProjectFilterTable!$A$29:$A$11459<>"",IF(ProjectFilterTable!$E$29:$E$11459=$G$29,IF(ProjectFilterTable!$F$29:$F$11459=$F$30,MATCH("~"&ProjectFilterTable!$A$29:$A$11459,ProjectFilterTable!$A$29:$A$11459&"",0))))),ROW(ProjectFilterTable!$A$29:$A$11459)-ROW(ProjectFilterTable!$A$29)+1),1))}

I do have a question tho'....What does the "~" do in the formula? How do I read that in human speak?

Thanks much,
Don

These are more or less re-writes. IF acts as a filter and for that reason more intuitive.

Tilde has been used to counteract adverse conditions which can obtain when the MATCH range consists of items with special meaning symbols like <, etc. MATCH, COUNTIF(S) and kindred functions act upon the special meaning of such a symbol, "~" allows to escape such meaning. Thus, we can have safely...

MATCH(ProjectFilterTable!$A$29:$A$11459,ProjectFilterTable!$A$29:$A$11459,0)

if special meaning symbols do not occur.

Hope this helps.

 

[ddander54]

Aladin,

Thanks for the response and the explanation!

 

[Aladin Akyurek]

Aladin,

Thanks for the response and the explanation!

You are welcome.