Formula to SUM distinct values based on a specific list

[Milos]

Hi there,

I am wanting a bit of help to refine a formula so that I can count the number distinct values in a certain range. This will assisting me ascertaining people hours for a project that I am involved with.

I am currently using this formula =SUMPRODUCT(1/COUNTIF(H3:K353,H3:K353)) which works well except I need to be able to exclude particular values in that range. Can anybody explain to me how I would refine this formula to only include specific values in this formula? The list of distinct values I want to count is: MA, MB, EMW, SR, KF, HS, KR, EW, CM, JR & HO

Cheers,
Milos

 

[Eric W]

Maybe:

=SUMPRODUCT(SIGN(COUNTIF(H3:K353,{"MA","MB","EMW","SR","KF","HS","KR","EW","CM","JR","HO"})))

 

[Milos]

Wow that is impressive! Thank you very much!

 

[Eric W]

Glad to help!

And I just realized it doesn't have to be SUMPRODUCT, it works just fine with a regular SUM:

=SUM(SIGN(COUNTIF(H3:K353,{"MA","MB","EMW","SR","KF","HS","KR","EW","CM","JR","HO"})))

 

[Milos]

Thanks Eric,

I have finally started to implement this. Unfortunately my idea of what I needed was wrong and I need to amend this formula.. I am not sure if I need to make a new thread for this or not? I am happy to do so.

I actually need to merge the original formula into COUNTIFS formula (I think). So that if column B:B has a specific number (e.g. 37) then the formula below will only SUM the list for those rows. I tried using an IF formula to no avail. I think I need to utilise a COUNTIFS but I have not been able to make it work.

Formula to:
COUNTIF(B:B,37) and then: SUM(SIGN(COUNTIF(H3:K353,{"MA","MB","EMW","SR","KF","HS","KR","EW","CM","JR","HO"})))

Cheers,
Milos

 

[Eric W]

I'm not quite clear on what you want. Consider this range:

Book1
	B	C	D	E	F	G	H	I	J	K
4	1						X	X	X	X
5	2							MA		MB
6	37						HO	SR
7	5
8	37							SR	SR
9	37							SR	KF
10	4
11	37							MA		X
Sheet3

First, is this a reasonable subset of your sheet? Next, what would the final result be? How many times would I count MA, HO, SR, KF, MB, X in that total?

 

[Milos]

Apologies for the confusion. Yes this a reasonable subset.

Based on the subset that you created, the ideal formula would get a result of 4 ( see cell C9).

1. The count would only search in rows with 37 so rows 1, 2, 4 and 7 are dismissed automatically etc.
2. Then based on the above formula it would dismiss duplicate SR results in cells I5, I6 and J5, only counting distinct values once.
3. Lastly it would dismiss 'x' in column J8 due to 'x' not being in the above formula list (e.g.{"MA","MB","EMW","SR","KF","HS","KR","EW","CM","JR","HO"}).

Hopefully this makes sense?

Cheers,
Milos

 

[Eric W]

Tricky! This is the kind of formula that's fun to figure out. It's sometimes hard to figure out which set of functions to use that don't result in a 3-D (or 4-D!) internal arrays. But it generally ends up being complicated and hard to follow and/or maintain. But try this:

Book1
	B	C	D	E	F	G	H	I	J	K
3	1						X	X	X	X
4	2							MA		MB
5	37						HO	SR
6	5
7	37							SR	SR
8	37							SR	KF
9	4
10	37							MA		X
11	Final Count Version 1:	4
12	Final Count Version 2:	4
Sheet1

Cell Formulas
Range	Formula
C11	C11	=SUM(SIGN(COUNTIFS(B:B,37,H:H,{"MA";"MB";"EMW";"SR";"KF";"HS";"KR";"EW";"CM";"JR";"HO"})+ COUNTIFS(B:B,37,I:I,{"MA";"MB";"EMW";"SR";"KF";"HS";"KR";"EW";"CM";"JR";"HO"})+ COUNTIFS(B:B,37,J:J,{"MA";"MB";"EMW";"SR";"KF";"HS";"KR";"EW";"CM";"JR";"HO"})+ COUNTIFS(B:B,37,K:K,{"MA";"MB";"EMW";"SR";"KF";"HS";"KR";"EW";"CM";"JR";"HO"})))
C12	C12	{=SUM(SIGN(MMULT(TRANSPOSE(ROW(H3:H353)^0),(B3:B353=37)*COUNTIFS(OFFSET(H3:K3,ROW(H3:H353)-ROW(H3),0),{"MA","MB","EMW","SR","KF","HS","KR","EW","CM","JR","HO"}))))}
Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces.

The C11 formula is longer, but probably more efficient, and easier to understand. But it's also a pain to maintain. If you add a new set of initials, you have to add it in 4 places. If you add another column to search, say L, then you have to add a whole new COUNTIFS to the formula.


The C12 formula is my attempt to shorten it. This requires row references instead of whole column references, so you have to change the 353 in 3 places if you add a row. But adding a new set of initials is as easy as adding it to the list once, and adding another column is as easy as changing H3:K3 to H3:L3. But this requires entry by pressing Control+Shift+Enter, and the OFFSET function in it makes it volatile, so there could be some performance issues. But try them out and see how they work. Maybe I (or someone else here) can come up with yet a better version.