COUNTIFS With Lists Of Multiple Criteria

[excelbytes]

I have the following formula in cell O2:

=SUM(COUNTIFS(Table2[Customer],$Y$3#,Table2[Market],$Z$3#,Table2[Function],DCP[[#Headers],[Project Manager]]))

Y3 has this formula which generates a list of unique Customers from a filtered table DCP:

=UNIQUE(FILTER(DCP[Customer],SUBTOTAL(3,OFFSET(DCP[[#Headers],[Customer]],ROW(DCP[Customer])-ROW(DCP[[#Headers],[Customer]]),0))))

Z3 has this formula which generates a list of unique Markets from a filtered table DCP:

=UNIQUE(FILTER(DCP[Market],SUBTOTAL(3,OFFSET(DCP[[#Headers],[Market]],ROW(DCP[Market])-ROW(DCP[[#Headers],[Market]]),0))))

When I have only one Customer in Y and one or multiple Markets in Z, it works fine, but when I have multiple Customers, I get incorrect results.

Any suggestions as to how I can modify my formula to accomplish what I am trying to do, which is get a count based on criteria of two lists plus a third single criteria. Is this possible?

 

[Fluff]

How about

=SUM(COUNTIFS(Table2[Customer],torow($Y$3#),Table2[Market],$Z$3#,Table2[Function],DCP[[#Headers],[Project Manager]]))

 

[excelbytes]

Fluff, that appears to work! I am familiar with the TOROW function, but WHY does it work in this scenario?

 

[Fluff]

When using two arrays one has to be vertical & the other horizontal.

 

[excelbytes]

Got it! Thanks!

 

[Fluff]

You're welcome & thanks for the feedback.