Help with countifs

[claire]

Hi everyone,

I am having a problem with a countifs function that I could do with your help on.... (to be fair, countifs may not be the best choice!)

I have a database of training and each entry is categorised as certain group type and then is allocated up to 3 recommendations (see below). I want to count the total types of recommendation for each group type. The problemis that because there can be up to three recommendations, it's not a simple count if.

I've attached a sample. The function I've been working from is: =COUNTIFS(A:A,$H$1,B:B,G2,C:C,G2,D,G2) but it just keeps returning a value of 0. I've tried a few edits, but still can't get it right!

Any tips gratefully received.

Cheers


Group type Primary Recommendation Secondary Recommendation Additional Recommendation
A 5 8 7
B 9 1 5
C 2 2 3
A 3 8
B 9 3 2
C 1 4 4
D 5 1 9
B 3 1 4
B 4
C 2 6 4
A 8 4 5
B 3 2 6
C 4 8 2
D 5 3 4
D 4 9
C 7 1 8
C 5 4 3
A 4 1 3
B 6 8 5
C 8 5 8
B 9

 

[Claus Busch]

Hi Claire,

Am Thu, 17 Jul 2014 07:51:11 -0700 (PDT) schrieb
(e-mail address removed):

I am having a problem with a countifs function that I could do with your help on.... (to be fair, countifs may not be the best choice!)

I have a database of training and each entry is categorised as certain group type and then is allocated up to 3 recommendations (see below). I want to count the total types of recommendation for each group type. The problem is that because there can be up to three recommendations, it's not a simple count if.

I've attached a sample. The function I've been working from is: =COUNTIFS(A:A,$H$1,B:B,G2,C:C,G2,D,G2) but it just keeps returning a value of 0. I've tried a few edits, but still can't get it right!

it is much more complicated.
You have combinations like
A, 1, 1, 1
A, 2, 1, 1
A, 1, 2, 1
A, 1, 1, 2
B, 1, 1, 1
B, 2, 1, 1
and so on

Better try it with a Pivot-Table
Look here:
https://onedrive.live.com/?cid=9378...#cid=9378AAB6121822A3&id=9378AAB6121822A3!326
for "Pivot"


Regards
Claus B.