CountIFs - multiple named ranges and two criteria

[jessi81882]

SUMPRODUCT(COUNTIFS(INDIRECT({"Reason1","Reason2"}),"duplicated order",({"Name1","Name2"}),A2))

Can anyone help me with the above code?

I want to look across 12 months (each month having its own sheet) for all the times worker X has put a duplicated order on.

I have all my ranges named - Reason1-12 and Name1-12 and the above formula works fine for the first criteria but I receive an error pop up from excel with the second criteria added on.

Thanks

 

[Aladin Akyurek]

Could you post a small sample from a sheet with a duplicated order?

 

[jessi81882]

It won't allow me.

I'll explain a bit more. I have 1 sheet for each month, Jan - Dec, all laid out identical. What happens is that issues during the month go into this sheet. There could be 100+ workers and there is a choice of 5 possible error codes.

I already have macros to pull out a unique list but I now need to perform a calculation on how many times across the 12 month period each worker appears in the same row as each error.

Sheet 1 Sheet 2
Name Reason Name Reason
Peter Duplicated Order Paul Other
Paul Other Peter Duplicated Order
Piper Duplicated Order
Jade Other

I need to be able to pull through that Peter has 2 entries for duplicated order across all sheets

 

[Aladin Akyurek]

It won't allow me.

I'll explain a bit more. I have 1 sheet for each month, Jan - Dec, all laid out identical. What happens is that issues during the month go into this sheet. There could be 100+ workers and there is a choice of 5 possible error codes.

I already have macros to pull out a unique list but I now need to perform a calculation on how many times across the 12 month period each worker appears in the same row as each error.

Sheet 1 Sheet 2
Name Reason Name Reason
Peter Duplicated Order Paul Other
Paul Other Peter Duplicated Order
Piper Duplicated Order
Jade Other

I need to be able to pull through that Peter has 2 entries for duplicated order across all sheets

Define SheetList as referring to a range which houses the names of the relevant sheets. When done, you can invoke:

=SUMPRODUCT(COUNTIFS(INDIRECT("'"&SheetList&"'!A:A"),$A2,INDIRECT("'"&SheetList&"'!B:B"),"duplicated order"))

 

[jessi81882]

Thank you thank you thank you

 

[Aladin Akyurek]

Thank you thank you thank you

You are welcome.