Counting specific criteria involving two columns...

[kbaucher]

I am having a hard time trying to figure out how do the following... Need a total number of the below specific criteria.

Column B (count any repeating number only once)
Column C (specific word)

Right now we are counting only column C, but the problem lies in the column B data. B is boxes that are numbered, and we only wish to count each number once... There may be 24 instances in C, but after assessing column B, there are really only 5. How can I make my formula show this?

I have tried SUM with FREQUENCY, and cannot get the sum to equal anything but 0!

 

[Jonmo1]

Welcome to the board...

Try sumproduct

=SUMPRODUCT(--(C1:C100="Specific word"),--(B1:B100=number))


Hope that helps.

 

[kbaucher]

Thanks jonmo, but my problem comes with the frequency of the number. I am not looking for a specific number in Column B, but making sure that each different number (based on the criteria from Column C) is counted only once.

 

[Jonmo1]

OK, I see...

Try this array formula entered with CTRL + SHIFT + ENTER

=SUM(IF(FREQUENCY(IF(C1:C100="SpecificWord",B1:B100),B1:B100),1))

 

[T. Valko]

I am having a hard time trying to figure out how do the following... Need a total number of the below specific criteria.

Column B (count any repeating number only once)
Column C (specific word)

Right now we are counting only column C, but the problem lies in the column B data. B is boxes that are numbered, and we only wish to count each number once... There may be 24 instances in C, but after assessing column B, there are really only 5. How can I make my formula show this?

I have tried SUM with FREQUENCY, and cannot get the sum to equal anything but 0!

Try this...

Book1
	A	B	C	D	E
1	No	1	1	Yes	4
2	No	3	2	No	6
3	No	4	3	_	_
4	No	4	_	_	_
5	No	4	_	_	_
6	No	5	4	_	_
7	No	8	5	_	_
8	No	8	_	_	_
9	No	10	6	_	_
10	No	10	_	_	_
11	No	10	_	_	_
12	Yes	3	1	_	_
13	Yes	4	2	_	_
14	Yes	7	3	_	_
15	Yes	7	_	_	_
16	Yes	10	4	_	_
17	Yes	10	_	_	_
18	Yes	10	_	_	_
19	Yes	10	_	_	_
20	Yes	10	_	_	_
Sheet1

I have the data sorted just to make it easy to see the results.

This array formula** entered in E1 and copied down to E2:

=SUM(IF(FREQUENCY(IF(A$1:A$20=D1,B$1:B$20),B$1:B$20),1))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

 

[kbaucher]

Thank you jonmo - you're formula worked!

 

[Jonmo1]

Glad to help, thanks for the feedback.