How do I find out how many times Group 1,2,3 used each test?

Using Excel 2000

A B C D E F

Group Test Group Test 1 Test 2 Test 3

1 Test 1 1

2 Test 3 2

3 Test 2 3

1 Test 1

2 Test 3

3 Test 3

Thanks!!!

In D2, then fill right and down. This is an array formula (commit with

Ctrl+Shift+Enter).

=SUM(($C2=$A$2:$A$7)*(D$1=$B$2:$B$7))

Hi

If you concatenate columns A and B, you can suffice with a simple COUNTIF

function.

--

Wigi

http://www.yqcomputer.com/ = Excel/VBA, soccer and music

If you concatenate columns A and B, you can suffice with a simple COUNTIF

function.

--

Wigi

http://www.yqcomputer.com/ = Excel/VBA, soccer and music

In D2:

=SUMPRODUCT(($B$2:$B$10=D$1)*($A$2:$A$10=$C2))

Copy across/fill down to populate. Adapt the ranges to suit.

Another alternative is to create a pivot on the source data in cols A & B,

with "Group" placed in ROW area, "Test" in COLUMN area and in DATA area

(Count of Test).

--

Max

Singapore

http://www.yqcomputer.com/

Downloads:17,400 Files:358 Subscribers:55

xdemechanik

---

=SUMPRODUCT(($B$2:$B$10=D$1)*($A$2:$A$10=$C2))

Copy across/fill down to populate. Adapt the ranges to suit.

Another alternative is to create a pivot on the source data in cols A & B,

with "Group" placed in ROW area, "Test" in COLUMN area and in DATA area

(Count of Test).

--

Max

Singapore

http://www.yqcomputer.com/

Downloads:17,400 Files:358 Subscribers:55

xdemechanik

---

Thanks!! In trying to generalize I kind of screwed up my question. What if

the name sof the tests were embedded with other text in that cell. John's

Test 1, Ken's Test 1, Jane's Test 3 etc...

I still need to count the number of Test 1s', but I can't do it by saying it

will equal the heading in column D.

Thanks for the help. You guys are great!!

the name sof the tests were embedded with other text in that cell. John's

Test 1, Ken's Test 1, Jane's Test 3 etc...

I still need to count the number of Test 1s', but I can't do it by saying it

will equal the heading in column D.

Thanks for the help. You guys are great!!

If col B contains the embedded data as you mentioned below,

then you could try this instead, in D2:

=SUMPRODUCT((ISNUMBER(SEARCH(D$1,$B$2:$B$10)))*($A$2:$A$10=$C2))

Copy D2 across/fill down to populate. Adapt the ranges to suit

--

Max

Singapore

http://www.yqcomputer.com/

Downloads:17,400 Files:358 Subscribers:55

xdemechanik

---

then you could try this instead, in D2:

=SUMPRODUCT((ISNUMBER(SEARCH(D$1,$B$2:$B$10)))*($A$2:$A$10=$C2))

Copy D2 across/fill down to populate. Adapt the ranges to suit

--

Max

Singapore

http://www.yqcomputer.com/

Downloads:17,400 Files:358 Subscribers:55

xdemechanik

---

1. COUNTIF or SUM with nested IF's

4. New IFS interface (jfs.ifs, udf.ifs for example)

5. Using nested IFs with CountIf

6. Formula for Multiple "IF's" or maybe "COUNTIF's"???

7. How do I countif with two ifs?

8. NESTED IF's and COUNTIF's in ACCESS'97 & attached EXCEL'97

9. Summing several IF's in a column.

10. File Size, nested Ifs and Sum

11. Sumproduct - multiple Sum ifs

12. Summing 2 ifs

13. Sum with 2 if's

6 post • Page:**1** of **1**