Solved

# MS Excel Conditional Table Counts

Posted on 2013-02-04
Medium Priority
218 Views
Hi there,

My first question on EE!

I have a series of tables (see attached file) and I need to be able to  count the the number of times each value appears against their respective row headers and table footers.

For example - in the table there are Tables called 'Scenario 1', 'Scenario 2' and 'Scenario 3'.

I need to produce a Summary Tables that counts the number of times each number appears in all 3 scenario tables against the respective row and footer identifiers.

For example, in Scenario 1, the number 1 (see highlighted cells) is present as an E/L , as a P/M and as an E/T.
In Scenario 2, the number 1 is present as an P/M,  E/M and an E/T
In Scenario 3, the number 1 is present as a P/L, E/T and E/M.

What I really need is the for 'Summary' table to populate itself - so that I can change things around in the top 3 tables and try and balance out the Summary tables across the column headers.
Example.xlsx
0
Question by:totalcruise

LVL 50

Accepted Solution

barry houdini earned 2000 total points
ID: 38851562
This formula in D39 should give the correct results

=SUMPRODUCT((\$D\$5:\$D\$10=LEFT(D\$38))*(\$E\$11:\$R\$11=RIGHT(D\$38))*(\$E\$5:\$R\$10=\$C39)+(\$D\$16:\$D\$21=LEFT(D\$38))*(\$E\$22:\$R\$22=RIGHT(D\$38))*(\$E\$16:\$R\$21=\$C39)+(\$D\$27:\$D\$32=LEFT(D\$38))*(\$E\$33:\$R\$33=RIGHT(D\$38))*(\$E\$27:\$R\$32=\$C39))

copy across and down - see attached

This assumes that all 3 tables are the same shape - if not then you can use a similar formula with 3 separate SUMPRODUCTS....

[Edit: 16 was missing from Summary table - I added it in]

regards, barry
Example-barry.xlsx
0

Author Closing Comment

ID: 38851731
Marvellous.
0

## Featured Post

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.