Count number of entries based on multiple criteria

Many members are unable to see images in the *.png format.

If you need to post an image post it in the *.jpg format.

Even better than posting images... post a SMALL sample file. That way we can test solutions directly in the file with the relevant data.

=SUMPRODUCT(($B$4:$B$11=$G4)*($C$4:$C$11=$H4)/COUNTIFS($B$4:$B$11,$B$4:$B$11,$C$4:$C$11,$C$4:$C$11,$D$4:$D$11,$D$4:$D$11))
Try this formula
Here $G4 is Category i,e. Yellow and $H4 is Month i,e. 1
see the attached file

Try this...

Data Range

	B	C	D	E
3	category	month	user	users for entry
4	yellow	1	abc	2
5	red	1	yyy	1
6	blue	1	zzz	2
7	yellow	1	abc	2
8	yellow	1	def	2
9	red	2	sss	1
10	orange	2	ggg	1
11	blue	1	ccc	2

This array formula** entered in E4 and copied down:

=SUM(IF(FREQUENCY(IF((B$4:B$11=B4)*(C$4:C$11=C4),MATCH(D$4:D$11,D$4:D$11,0)),ROW(D$4:D$11)-ROW(D$4)+1),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.

=if(countifs($b$4:$b$11,b4,$c$4:$c$11,c4)*countif($d$4:$d$11,d4)=1,1,2)

Originally Posted by jturner12

Is there a way to make it B:B and C:C etc. so it covers new records too?

You should avoid using entire columns as references with this formula.

Just make the references larger (but not too large!) in anticipation of additional data. Then use this version which includes a test for empty cells.

=SUM(IF(FREQUENCY(IF((B$4:B$100=B4)*(C$4:C$100=C4),IF(D$4:D$100<>"",MATCH(D$4:D$100,D$4:D$100,0))),ROW(D$4:D$100)-ROW(D$4)+1),1))

Still array entered.

You're welcome. Thanks for the feedback!


If your question has been solved please mark the thread as being solved.

In the menu bar above the very first post select Thread Tools, then select Mark this thread as solved.