COUNTIF multiple criteria with different ranges across multiple sheets

0
votes

I have data on different sheets.

Names of the sheets at a4-a9

Sheet1
Sheet2
sheet3

In column C contain "name of bank" and in column L contain status (case closed, case open).

Example: I want to count for 'bankA' with status 'case open' across all sheet.

below is the formula I'm using but failed. Please help!

=SUMPRODUCT(COUNTIFS(INDIRECT("'"&$A$4:$A$9&"'!C:C"),"bankA",INDIRECT("'"&$A$4:$A$9&"'!L:L"),"Case Open"))
2
google-sheetsgoogle-sheets-formula
What error are you getting? Can you isolate what part of the formula is not working by evaluating it? The formula as is worked for me. – BigBen
it only count for the sheet1 only. not across the other sheet – ain

2 Answers

0
votes

enter image description here

Assuming you have named your list of sheets 'sheets` and your inputs are as stated:

 =SUMPRODUCT(
 COUNTIFS(
 INDIRECT("'"&sheets&"'!"&"C4:C9"),"BankA",
 INDIRECT("'"&sheets&"'!"&"L4:L9"),"case open"))
0
votes
=SUM(COUNTIFS(
 INDIRECT(A4&"!C:C"), "BankA",
 INDIRECT(A4&"!L:L"), "case open")+COUNTIFS(
 INDIRECT(A5&"!C:C"), "BankA",
 INDIRECT(A5&"!L:L"), "case open")+COUNTIFS(
 INDIRECT(A6&"!C:C"), "BankA",
 INDIRECT(A6&"!L:L"), "case open"))

0