Countifs with multiple named ranges as criteria

[mmytb]

Hi all,

I've written the following formulae using multiple named ranges (Weekdays and DestinationOtherCountry).
=SUMPRODUCT(COUNTIFS('Current Month'!$C:$C,Calculations!L$2,'Current Month'!$F:$F,Weekdays,'Current Month'!$G:$G,Calculations!$A$2,'Current Month'!$I:$I,$F$6,'Current Month'!$J:$J,DestinationOtherCountry))
It works with one without the other, either way round.
I found online that countifs don't work with more than one named range, which would explain the above.
Anyone got any ideas?
I cant share the excel itself. Apologies.

But a breakdown of the excel and function is as follows:
The excel records employees work activities. Each row being a new day.
Column C = Name
Column F = Day of week
Column G = Location
Column I = Activity (workday/nonworkday)
Column J = Destination location if someone travelled
And all criteria following the ranges in the function are all according.

I thought of trying to create a Boolean function for one of the conditions.
Lets say - for a specific person, for this month, if there is a row with a destination location return true - but im not quite sure how to do that.

Hope this isnt too long, im new to this forum.

Thanks

[Fluff13]

Try

Please Login or Register  to view this content.

[mmytb]

Thanks for the reply
That didnt work
And not clear to me what your trying to do with the transpose

[Fluff13]

In that case can you please post a workbook that shows the problem.

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