countifs with variable criteria

[cjcass]

Hi,
Is there a formula that I could put into cell K11 that will count the number of occurrences based on the variable criteria entered into the table J8:K9?
Am looking for a formula solution in this instance please.
Many thanks,

 

[Marcelo Branco]

Try

=COUNTIFS(INDEX(B4:H25,0,MATCH(J8,B3:I3,0)),K8,INDEX(B4:H25,0,MATCH(J9,B3:I3,0)),K9)

M.

 

[cjcass]

Brilliant!
Works great, thanks very much

 

[Marcelo Branco]

You are welcome. Glad to help.

M.

 

[cjcass]

Have just thought of something.
Is there a way that this formula can be adapted so if one of the two blocks of criteria is empty (ie. J8:K8 or J9:K9 contain nothing) then the formula will calculate using just the criteria that is available?
I know how to use a long string of nested IF statements IF(J8="", etc.. but was wondering if there was a simpler way (without vba). That way I could adapt it further with more criteria blocks without huge formulas.
Any help would be much appreciated.
Thanks

 

[Marcelo Branco]

Maybe...

=COUNTIFS(INDEX(B4:H25,0,MATCH(J8,B3:H3,0)),IF(K8<>"",K8,"<>"),INDEX(B4:H25,0,MATCH(J9,B3:H3,0)),IF(K9<>"",K9,"<>"))

M.