Sum(countifs) with multiple array criteria

Question

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 reference 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 experience with VBA so would like to avoid that if I can.

Answer 1

You can do complex counting logic using pivot tables in excel. There are lots of different drop down options are available in the pivot table set up including different filter, grouping, counting and summation options.

Answer 2

Let's say that the range containing the list of companies is named CompanyArray, and that the range containing the list of names is named NameArray. If CompanyArray refers to horizontal range of cells and NameArray refers to a vertical range of cells, try...

=SUMPRODUCT(COUNTIFS(InspectionCompany,CompanyArray,InspectionType,"*Part of Inspection Title*",InspectionCreatedBy,NameArray))

If CompanyArray and NameArray both refer to a vertical range of cells, try the following formula that needs to be confirmed with CONTROL+SHIFT+ENTER...

=SUM(COUNTIFS(InspectionCompany,TRANSPOSE(CompanyArray),InspectionType,"*Part of Inspection Title*",InspectionCreatedBy,NameArray))

Hope this helps!