Countif to stop counting.

[Roamingsmile]

Hi everyone, a new user here...

I’ve got a cell (g3) which contains the following-

COUNTIF(‘D1’!A41:N45, “1.1.2”)

1.1.2 can be entered unlimited times on the other sheet which Is fine and returns my count.

however this number needs to only be counted up to 2.

I gather it needs to be some sort of min(countif but I can’t work exactly.

this is one of many of my problems as I’m making (what I think lol) is a complex workbook! So if anyone wants to help, I’d be appreciative!

 

[Eric W]

Welcome to the forum!

Try:

=MIN(COUNTIF(‘D1’!A41:N45, “1.1.2”), 2)

 

[steve the fish]

To get a maximum you would use =MIN(2, yourformula)

 

[Roamingsmile]

Welcome to the forum!

Try:

=MIN(COUNTIF(‘D1’!A41:N45, “1.1.2”), 2)

That worked perfectly thanks! I was so close just putting the 2 in the wrong place!!!

 

[Eric W]

Glad we could help!

 

[Roamingsmile]

Ah so, I realised after that this formula needs to count from multiple pages... it doesn’t like it if I make countif into countifs and add multiple criteria, it brings up the NAME error. I assume it’s trying to find the 1.1.2 on every sheet which it won’t find?

 

[steve the fish]

Show us the formula creating a name error.

 

[Eric W]

You're looking on multiple sheets? You may need to have a list of the sheets. Then you can use a formula like this:

Book1
	A	B
1	3	D1
2	2	D2
3		D3
D2

Cell Formulas
Range	Formula
A1	A1	=SUMPRODUCT(COUNTIF(INDIRECT("'"&B1:B3&"'!A41:N45"),"1.1.2"))
A2	A2	=MIN(SUMPRODUCT(COUNTIF(INDIRECT("'"&B1:B3&"'!A41:N45"),"1.1.2")),2)