Table Lookup Formula

Question

I need help with a formula to complete cells C21-C25 and D21-D25 in the table below.

The Cells in C4-J4 are the names of banks. These are the same names that appear in column B21-25. In Cells C21-C25 I want a count of the number of accounts that we have at each bank, even if the account has no money in it. In Cells D21-D25 I'd like the number of accounts at each bank that have a balance.

Any suggestions would be appreciated.

Answer 1

Formula in cell 21:

=COUNT(IF($C$5:$J$17<>"",IF($B21=$C$4:$J$4,$C$5:$J$17,""),""))

Formula in cell D21:

=COUNT(IF($C$5:$J$17<>0,IF($B21=$C$4:$J$4,$C$5:$J$17,""),""))

Enter as array formula if not using Excel 365.

Regards,

Amit Tandon

http://www.excelanytime.com/

Answer 2

Hi,

Enter this formula in cell C21 and drag down

=SUM(MMULT(($C$5:$J$17<>"")*($C$4:$J$4=$B21),{1;1;1;1;1;1;1;1}))

Enter this formula in cell D21 and drag down

=SUM(MMULT(($C$5:$J$17>0)*($C$4:$J$4=$B21),{1;1;1;1;1;1;1;1}))

Hope this helps.

Answer 3

Excellent... exactly what I needed. Thanks so much.

Answer 4

Good try but that only works for the first column for each bank. Your formula for bank "BBB" returns the number 2. Which is correct for column D, but Bank BBB also has accounts in columns E&F so the correct number of total BBB accounts is 11 and 6 of them have money in them.

Thanks for trying though.

Answer 5

In D21:

=COUNTIF(INDEX($C$5:$J$17,,MATCH(B21,$C$4:$J$4,FALSE)),">0")

In C21:

=COUNTIF(INDEX($C$5:$J$17,,MATCH(B21,$C$4:$J$4,FALSE)),"-")+D21

Copied down