Counta and Countifs Ignore Formula

[ozbeachbum]

Hi,
For L279 I need to calculate IF $D280:$D302 is >0 & L280:L302 is >0
then return the number of cells >0 in L280:L302 (3). Otherwise Zero.

For P279 I need to calculate IF $D280:$D302 is >0 & P280:P302 is >0
then return the number of cells >0 in P280:P302 (1). Otherwise Zero.

I have tried numerous combinations but just can't get a formula that will work for both locations.
Any help greatly appreciated.

241118 counta if ifs.xlsx
	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
279	03		03		04				03		00		03		00
280	01	0	SEK	CBD	ASX		0	1	X0050285073			1	6622 | 88488573
281
282
283
284
285
286	01	0	SKC	CBD	ASX		0	1	X0050285073
287
288
289
290
291
292	01	0	SOL	CBD	ASX		F	1	X0050285073
293
294
295
296
297
298	00	0	SYM	CBD	ASX		S	1	X0050285073
299
300
301
302
303
304	For L279 I need to calculate IF $D280:$D302 is >0 & L280:L302 is >0
305	then return the number of cells >0 in L280:L302 (3). Otherwise Zero.
306
307	For P279 I need to calculate IF $D280:$D302 is >0 & P280:P302 is >0
308	then return the number of cells >0 in P280:P302 (1). Otherwise Zero.
Blank

Cell Formulas
Range	Formula
D279	D279	=COUNTA($D280:$D302)-COUNTIF($D280:$D302,0)
F279,R279,N279	F279	=COUNTIFS($D280:$D302,">0",F280:F302,"<>")
H279	H279	=COUNTA(H280:H302)
D280,D298,D292,D286	D280	='[01 REGISTER holding co.xlsx]Primary Entry'!D280
E280,G280,E298,G298,E292,G292,E286,G286	E280	='[01 REGISTER holding co.xlsx]Primary Entry'!J280
F280,F298,F292,F286	F280	='[01 REGISTER holding co.xlsx]Primary Entry'!P280
H280,H298,H292,H286	H280	='[01 REGISTER holding co.xlsx]Primary Entry'!N280
J280,J298,J292,J286	J280	='[01 REGISTER holding co.xlsx]Primary Entry'!H286
L279,P279	L279	=IF(COUNTA(L280:L296)-COUNTIF(L280:L296,0)=0,0,COUNTIFS($D280:$D296,">0",L280:L296,"<>"))
L280,L298,L292,L286	L280	=IF(K280=1,$R$25,"")
P280,P298,P292,P286	P280	=IF(O280=1,$R$35&" | "&$R$37,"")

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
P279	Cell Value	>0	text	YES
L279	Cell Value	>0	text	YES
H279	Cell Value	=1	text	YES
H279	Cell Value	=2	text	YES
H279	Cell Value	=3	text	YES
H279	Cell Value	=4	text	YES
H279	Cell Value	=7	text	YES
H279	Cell Value	=8	text	YES
R279,N279	Cell Value	>0	text	YES
F279	Expression	=F279<$D279	text	YES
F298	Expression	=AND(F298="TLS",D298=0)	text	YES
F298	Expression	=AND(F298="IAG",D298=0)	text	YES
F298	Expression	=AND(ISTEXT(F298),D298=0)	text	YES
F298	Cell Value	="TLS"	text	YES
F298	Cell Value	="IAG"	text	YES
F298	Expression	=ISTEXT(F298)	text	YES
F298	Cell Value	=0	text	YES
H298	Cell Value	="ASX"	text	NO
H298	Cell Value	="LSX"	text	YES
H298	Cell Value	=NZX	text	YES
H298	Cell Value	="NYX"	text	YES
H298	Cell Value	=0	text	NO
F292	Expression	=AND(F292="TLS",D292=0)	text	YES
F292	Expression	=AND(F292="IAG",D292=0)	text	YES
F292	Expression	=AND(ISTEXT(F292),D292=0)	text	YES
F292	Cell Value	="TLS"	text	YES
F292	Cell Value	="IAG"	text	YES
F292	Expression	=ISTEXT(F292)	text	YES
F292	Cell Value	=0	text	YES
H292	Cell Value	="ASX"	text	NO
H292	Cell Value	="LSX"	text	YES
H292	Cell Value	=NZX	text	YES
H292	Cell Value	="NYX"	text	YES
H292	Cell Value	=0	text	NO
F286	Expression	=AND(F286="TLS",D286=0)	text	YES
F286	Expression	=AND(F286="IAG",D286=0)	text	YES
F286	Expression	=AND(ISTEXT(F286),D286=0)	text	YES
F286	Cell Value	="TLS"	text	YES
F286	Cell Value	="IAG"	text	YES
F286	Expression	=ISTEXT(F286)	text	YES
F286	Cell Value	=0	text	YES
H286	Cell Value	="ASX"	text	NO
H286	Cell Value	="LSX"	text	YES
H286	Cell Value	=NZX	text	YES
H286	Cell Value	="NYX"	text	YES
H286	Cell Value	=0	text	NO
F280	Expression	=AND(F280="TLS",D280=0)	text	YES
F280	Expression	=AND(F280="IAG",D280=0)	text	YES
F280	Expression	=AND(ISTEXT(F280),D280=0)	text	YES
F280	Cell Value	="TLS"	text	YES
F280	Cell Value	="IAG"	text	YES
F280	Expression	=ISTEXT(F280)	text	YES
F280	Cell Value	=0	text	YES
H280	Cell Value	="ASX"	text	NO
H280	Cell Value	="LSX"	text	YES
H280	Cell Value	=NZX	text	YES
H280	Cell Value	="NYX"	text	YES
H280	Cell Value	=0	text	NO
N280,N286,N292,N298,R280,R286,R292,R298	Expression	=AND($D280=0,N280>0)	text	NO
L280,P280,L286,L292,L298,P286,P292,P298	Expression	=AND($D280=0,L280>0)	text	YES
L280,P280,L286,L292,L298,P286,P292,P298	Cell Value	>0	text	YES
J298	Cell Value	="S"	text	NO
J298	Cell Value	="T"	text	NO
J298	Cell Value	="F"	text	NO
J292	Cell Value	="S"	text	NO
J292	Cell Value	="T"	text	NO
J292	Cell Value	="F"	text	NO
J286	Cell Value	="S"	text	NO
J286	Cell Value	="T"	text	NO
J286	Cell Value	="F"	text	NO
J280	Cell Value	="S"	text	NO
J280	Cell Value	="T"	text	NO
J280	Cell Value	="F"	text	NO

 

[6StringJazzer]

For L279 I need to calculate IF $D280:$D302 is >0 & L280:L302 is >0

These are ranges. It doesn't make sense to say that a range is >0.
Also column L is text, not numeric, so it especially doesn't make sense to say that L280:L302 is >0.

Please give an example of data that would meet these conditions.

 

[Peter_SSs]

Try this in L279 & copy it to P279

=COUNTIFS($D280:$D302,">0",L280:L302,"?*")

 

[ozbeachbum]

These are ranges. It doesn't make sense to say that a range is >0.
Also column L is text, not numeric, so it especially doesn't make sense to say that L280:L302 is >0.

Please give an example of data that would meet these conditions.

Thanks apricate your comments and will look into it.

 

[ozbeachbum]

Try this in L279 & copy it to P279

=COUNTIFS($D280:$D302,">0",L280:L302,"?*")

Thanks as always, works perfectly.

 

[Peter_SSs]

No problem. Glad to help.