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))

 

[jasonb75]

You can do what you want with a maximum of 2 ranges by transposing one of them, for example
=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],TRANSPOSE(lu_tbl_exec))

For more than 2 ranges you would need to use more complex and less efficient array formulas, which would need to be based on a number of factors such as excel version (which functions are available to use) and the formats of the data. In many cases text strings need to be handled differently to numeric values, especially where partial matches are involved.

 

[jorismoerings]

Hi,

Just to add to @jasonb75: i was thinking along the same line but this solution will work based on the assumption the dynamic table only holds ONE column.

 

[leearmitage]

You can do what you want with a maximum of 2 ranges by transposing one of them, for example
=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],TRANSPOSE(lu_tbl_exec))

For more than 2 ranges you would need to use more complex and less efficient array formulas, which would need to be based on a number of factors such as excel version (which functions are available to use) and the formats of the data. In many cases text strings need to be handled differently to numeric values, especially where partial matches are involved.

Thanks Jason. Unfortunately that didn't work. I think because there were too many words in the transposed array? I did try it with COLUMN E and the lu_tbl_from instead (which only has a maximum of 4 possible entries), but the results were still spurious. I am happy to try a CSE array formula if neccessary. I'm using Excel 2016

 

[leearmitage]

Hi,

Just to add to @jasonb75: i was thinking along the same line but this solution will work based on the assumption the dynamic table only holds ONE column.

Yeah just one column in each of the lookup tables!

 

[Fluff]

I'm using Excel 2016

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using saves having to mention it. (Don’t forget to scroll down & ‘Save’)

 

[leearmitage]

Yeah just one column in each of the lookup tables!

Yep, just done thanks for pointing it out!

 

[jasonb75]

but the results were still spurious.

Are there any blanks in the dynamic tables?

 

[leearmitage]

Are there any blanks in the dynamic tables?

No, no blanks. It was weird. It seems to only give the result for the first item in the TRANSPOSE section

 

[jasonb75]

Did you try it with CSE? As you're using 2016, that is likely to be necessary.