CODEWITHSUNDEEP
excelexcel-formulacountif
Sam
Sam

Reputation: 497

Count values in other tables based on values in source table

I'm sure it's been asked before but I can't find it... In Excel, can I use a formula to COUNTIF or SUMIF from other tables based on a value in the first column of the source table? Example: 

Index         StepCount     totalPass   totalFail
tblUserLogin    1               13          3
tblPMOUser      1               13          3
tblSAUser       1               13          3

The formula in B2 is something like:
Count the steps in the table named A2
Thinking that I could build a string to equal the criteria, I tried this: =COUNTA("[@[Index]]"&"[Step]") to no avail.

C2 and D2 would be:
Count the number of PASS/FAIL in column D from the table named A2

Two outcomes that I hope for:
1) Point me in the right direction formula-wise
2) Tell me that it can't be done without VB

Upvotes: 0

Views: 348

Answers (1)

zipa
zipa

Reputation: 27889

So for your Step you need:

=COUNTA(INDIRECT(A2&"[Step]"))

Just apply the same for the other functions.

Or if is a table, use:

=COUNTA(INDIRECT([Index]&"[Step]"))

Upvotes: 1

Related Questions