SUMPRODUCT COUNTIFS multiple columns with multiple criteria held in a dynamic list

[leearmitage]

I have the following formula which works correctly. It counts the number of times the word "error" is found in column A when column B is equal to a particular date held in cell $D$2, and Column C is equal to any value in a table called lu_tbl_branch. That table is dynamic, so sometimes it may have 1 entry, other times it could be 30 or more (so I can't hard code the values)

=SUMPRODUCT(COUNTIFS(
loading_Appended_Source[Column A],"*error*",
loading_Appended_Source[Column B],D$2,
loading_Appended_Source[Column C],lu_tbl_branch))


I would like to adjust the formula so that I can also refer to other dynamic tables (called lu_tbl_exec and lu_tbl_from). I've tried the following but no success, so hoping you can help me!


=SUMPRODUCT(COUNTIFS(
loading_Appended_Source[[Column A],"*error*",
loading_Appended_Source[[Column B],D$2,
loading_Appended_Source[[Column C],lu_tbl_branch,
loading_Appended_Source[[Column D],lu_tbl_exec,
loading_Appended_Source[[Column E],lu_tbl_from))

 

[leearmitage]

Yep, it did work when CSE entered, thanks!

Unfortunately though the array length is too long once the items in the table are transposed. So I think I may have to come up with a completely new solution, GETPIVOTDATA perhaps. I hate it when Excel beats me!

 

[jasonb75]

Unfortunately though the array length is too long once the items in the table are transposed.

If I'm remember correctly, that means over 65636 rows of data in the transposed table. If all of the tables are of similar size then you could encounter problems whatever you try.

In theory, this should work, in reality it's likely to think about it for ages then give up.

=SUMPRODUCT(ISNUMBER(SEARCH("error",loading_Appended_Source[[Column A]))*
(loading_Appended_Source[[Column B]=D$2)*
ISNUMBER(SEARCH(loading_Appended_Source[[Column C],lu_tbl_branch))*
ISNUMBER(SEARCH(loading_Appended_Source[[Column D],lu_tbl_exec))*
ISNUMBER(SEARCH(loading_Appended_Source[[Column E],lu_tbl_from)))

Adding helper columns to the loading_Appended_Source table to reduce the workload of the formula might help, but it's still going to be a lot of processing.
vba might be a better option, but what you would need to achieve this is above my pay grade.

 

[leearmitage]

Yeah, thanks for your time with this Jason, greatly appreciated. Think I'll have to go with a GETPIVOTDATA option with a bunch of slicers as will be quicker!