Hi Everyone,

This is my first shot at excel. Have tried everything to get this to
work.

I have an external workbook called Master_Platform_14.1.1PT and a
Sheet1 (several worksheets all the same format with different data)

The workbook contains 1500 rows and 35 columns
I am only interested in getting the counts for a few of the columns
and summarizing in 2 seperate worksheets in new workbook as follows:

Columns A and B are hardcoded so I have the MR and Name I just can't
seem to count the total occurrences using The dynamic name =
MR_14.1.1PT or Tester_14.1.1PT

Sheet1 A B
C D E
1 "MR Name Total_Tests
Executed Fail "
2 NFMYFEAT Jane Doe
3 RFFEAT John Smith

Sheet2 A B
C C E
1 "Jane Doe"
Total_tests Executed Fail "
2 NFMYFEAT
3 RFMYOLDFEAT

External Data [Master_Platform_141.PT.xlsx]Sheet1
A B C
D E F
1 Release MR Description Name
Executed Fail .....
2 14.1.1PT NFMYFEAT blah blah Jane Doe
True False
3 14.1.1PT RFFEAT blah blah John Smith
False False
2 14.1.1PT RFSOMEFEAT blah blah Jane Doe
True False

So I thought I could use dymamic names to capture the data. In the
new workbook I created (its changed again but this is where I am
at...)

Raw!B3 =COUNTA([Master_Platform_14.1.1PT.xlsx]Sheet1!$A:$A)

Name=
MR_14.1.1PT
=[Master_Platform_14.1.1PT.xlsx]Sheet1!$C$2:INDEX
([Master_Platform_14.1.1PT.xlsx]Sheet1!$C:$C,Raw!$B$3)

Name=
Tester_14.1.1PT
=[Master_Platform_14.1.1PT.xlsx]Sheet1!$X$2:INDEX
([Master_Platform_14.1.1PT.xlsx]Sheet1!$X:$X,Raw!$B$3)

In the new sheet I am trying to get counts of

Number of Tests by MR (tried SUMPRODUCT, COUNTIF, IF....) I don't
know what I am doing
=counta(--(MR_14.1.1PT=$A2))

Number of Tests by "Name" for a specific "MR"
--- Name is particular problem I think I might need to do =LEFT (... -
first name good enough)
-- Name=$A2 and MR=$B2


Thank you for any help that can be provided.
Sorry for the long email but the files are large and truely am
confused and have to get this sheet done soooooon. I have googled
till I can't google anymore.....

Thanks
d.

On Jun 30, 5:16*pm, dz wrote:
Hi Everyone,

This is my first shot at excel. *Have tried everything to get this to
work.

I have an external workbook called Master_Platform_14.1.1PT and a
Sheet1 (several worksheets all the same format with different data)

The workbook contains 1500 rows and 35 columns
I am only interested in getting the counts for a few of the columns
and summarizing in 2 seperate worksheets in new workbook as follows:

Columns A and B *are hardcoded *so I have the MR and Name I just can't
seem to count the total occurrences using The dynamic name =
MR_14.1.1PT or Tester_14.1.1PT

* * *Sheet1 * * * * * A * * * * * * * * * * *B
C * * * * * * * *D * * * * * * E
* * 1 * * * * * * * * * *"MR * * * * * * * * * Name * *Total_Tests
Executed * Fail *"
* * 2 * * * * * * * * * *NFMYFEAT * * Jane Doe
* * 3 * * * * * * * * * *RFFEAT * * * * *John Smith

* * *Sheet2 * * * *A * * * * *B
C * * * * * * * * * C * * * * * * E
* * 1 * * * * * *"Jane Doe"
Total_tests * * *Executed * *Fail * "
* * 2 * * * * * * * * * * * NFMYFEAT
* * 3 * * * * * * * * * * *RFMYOLDFEAT

External Data [Master_Platform_141.PT.xlsx]Sheet1
* * * * *A * * * * * B * * * * * * * * * *C
D * * * * * * * * *E * * * * * * * F
1 * * Release * MR * * * * * * * * *Description * Name
Executed * Fail .....
2 * * 14.1.1PT *NFMYFEAT * * blah blah * * * Jane Doe
True * * * * *False
3 * * *14.1.1PT *RFFEAT * * * * blah blah * * *John Smith
False * * * *False
2 * * 14.1.1PT *RFSOMEFEAT blah blah * * * Jane Doe
True * * * * *False

So I thought I could use dymamic names to capture the data. *In the
new workbook I created (its changed again but this is where I am
at...)

Raw!B3 =COUNTA([Master_Platform_14.1.1PT.xlsx]Sheet1!$A:$A)

Name=
MR_14.1.1PT
=[Master_Platform_14.1.1PT.xlsx]Sheet1!$C$2:INDEX
([Master_Platform_14.1.1PT.xlsx]Sheet1!$C:$C,Raw!$B$3)

Name=
Tester_14.1.1PT
=[Master_Platform_14.1.1PT.xlsx]Sheet1!$X$2:INDEX
([Master_Platform_14.1.1PT.xlsx]Sheet1!$X:$X,Raw!$B$3)

In the new sheet I am trying to get counts of

Number of Tests by MR *(tried SUMPRODUCT, COUNTIF, IF....) I don't
know what I am doing
=counta(--(MR_14.1.1PT=$A2))

Number of Tests by "Name" for a specific "MR"
--- Name is particular problem I think I might need to do =LEFT (... -
first name good enough)
-- Name=$A2 *and MR=$B2

Thank you for any help that can be provided.
Sorry for the long email but the files are large and truely am
confused and have to get this sheet done soooooon. *I have googled
till I can't google anymore.....

Thanks
d.

Not sure what I did all night but now my SUMPRODUCT(--(MR_14.x.1PT=
$B2)) is giving me the correct number for te last 5 hours it only
return "1" . Sorry.

Working on count for MR= and Tester= hopefully that will work as
well :)

Thanks again!

On Jun 30, 6:31*pm, dz wrote:
On Jun 30, 5:16*pm, dz wrote:



Hi Everyone,

This is my first shot at excel. *Have tried everything to get this to
work.

I have an external workbook called Master_Platform_14.1.1PT and a
Sheet1 (several worksheets all the same format with different data)

The workbook contains 1500 rows and 35 columns
I am only interested in getting the counts for a few of the columns
and summarizing in 2 seperate worksheets in new workbook as follows:

Columns A and B *are hardcoded *so I have the MR and Name I just can't
seem to count the total occurrences using The dynamic name =
MR_14.1.1PT or Tester_14.1.1PT

* * *Sheet1 * * * * * A * * * * * * * * * * *B
C * * * * * * * *D * * * * * * E
* * 1 * * * * * * * * * *"MR * * * * * * * * * Name * *Total_Tests
Executed * Fail *"
* * 2 * * * * * * * * * *NFMYFEAT * * Jane Doe
* * 3 * * * * * * * * * *RFFEAT * * * * *John Smith

* * *Sheet2 * * * *A * * * * *B
C * * * * * * * * * C * * * * * * E
* * 1 * * * * * *"Jane Doe"
Total_tests * * *Executed * *Fail * "
* * 2 * * * * * * * * * * * NFMYFEAT
* * 3 * * * * * * * * * * *RFMYOLDFEAT

External Data [Master_Platform_141.PT.xlsx]Sheet1
* * * * *A * * * * * B * * * * * * * * * *C
D * * * * * * * * *E * * * * * * * F
1 * * Release * MR * * * * * * * * *Description * Name
Executed * Fail .....
2 * * 14.1.1PT *NFMYFEAT * * blah blah * * * Jane Doe
True * * * * *False
3 * * *14.1.1PT *RFFEAT * * * * blah blah * * *John Smith
False * * * *False
2 * * 14.1.1PT *RFSOMEFEAT blah blah * * * Jane Doe
True * * * * *False

So I thought I could use dymamic names to capture the data. *In the
new workbook I created (its changed again but this is where I am
at...)

Raw!B3 =COUNTA([Master_Platform_14.1.1PT.xlsx]Sheet1!$A:$A)

Name=
MR_14.1.1PT
=[Master_Platform_14.1.1PT.xlsx]Sheet1!$C$2:INDEX
([Master_Platform_14.1.1PT.xlsx]Sheet1!$C:$C,Raw!$B$3)

Name=
Tester_14.1.1PT
=[Master_Platform_14.1.1PT.xlsx]Sheet1!$X$2:INDEX
([Master_Platform_14.1.1PT.xlsx]Sheet1!$X:$X,Raw!$B$3)

In the new sheet I am trying to get counts of

Number of Tests by MR *(tried SUMPRODUCT, COUNTIF, IF....) I don't
know what I am doing
=counta(--(MR_14.1.1PT=$A2))

Number of Tests by "Name" for a specific "MR"
--- Name is particular problem I think I might need to do =LEFT (... -
first name good enough)
-- Name=$A2 *and MR=$B2

Thank you for any help that can be provided.
Sorry for the long email but the files are large and truely am
confused and have to get this sheet done soooooon. *I have googled
till I can't google anymore.....

Thanks
d.

Not sure what I did all night but now my SUMPRODUCT(--(MR_14.x.1PT=
$B2)) is giving me the correct number for te last 5 hours it only
return "1" . * Sorry.

Working on count for MR= and Tester= * hopefully that will work as
well :)

Thanks again!

ALL GOOD - Thanks for being here!!!
My issue was $B2 had a type (ugh!!!!) I could not see it (NFJASS vs
JFJAAS) what you get for long hours..... to stupid to try another
cell :(


SUMPRODUCT is great