Sum(countifs( with multiple array criteria

[TimberMate]

Hi All,

I have an excel spread sheet that summarises a Table of data, currently using the Sum(countifs()) functions to look up columns and Count the number of time specific Criteria are meet. two of the criteria are in the form of Arrays. i would like to change these to Named Ranges and referance them so i can more easily maintain the form.
Had a look around online with no luck. Tried a few versions of Sumproduct but couldn't figure it out.
SUM(COUNTIFS(InspectionCompany,{"Company 1","Company 2",...,"Company 8"},InspectionType,"Part of Inspection Title",InspectionCreatedBy,{"Name 1";...;"Name X"})))
in this case the Function works and i get the expected result But i would like to change the Company Array and CreatedBy Array to both be Named Ranges so i can just update the one spot and not have to worry about replicating the function. i don't have much experiance with VBA so would like to avoid that if i can.

Thanks.

 

[Marcelo Branco]

Welcome to Mr Excel forum

Try something like this
=SUMPRODUCT(COUNTIFS(InspectionCompany,Range1,InspectionType,"Part of Inspection Title",InspectionCreatedBy,Range2))

where
Range1 is a horizontal named range that contains the companies
Range2 is a vertical named range that contains the names

Hope this helps

M.