Greater Than and Less than ZERO

[frankee_gee]

Hello MRE, looking for assistance with the following formula(s). Need to count the Difference Columns where it's greater or less than ZERO.

I came up with the following formulas below.



1 .

=COUNTIFS(E3:E30:H3:H30:K3:K30:N3:N30,"<0")+COUNTIFS(E3:E30:H3:H30:K3:K30:N3:N30,">0")

= 91 (total combined, expecting 11)


2.

=COUNTIFS(E3:E30:H3:H30:K3:K30:N3:N30,"<0")

= 3 (correct)


3.

=COUNTIFS(E3:E30:H3:H30:K3:K30:N3:N30,">0")

= 88 (expecting 8)

Would like to understand why ">0" is returning a bad number.

Entered	Captured	DIFFERENCE	Entered	Captured	DIFFERENCE	Entered	Captured	DIFFERENCE	Entered	Captured	DIFFERENCE
80	67	-13	24	24	0	0	0	0	0	0	0
0	0	0	32	32	0	32	32	0	0	0	0
0	0	0	40	40	0	40	40	0	0	0	0
110	110	0	116	116	0	32	32	0	0	0	0
91	95	4	99	99	0	0	0	0	0	0	0
47	47	0	40	40	0	0	0	0	0	0	0
110	118	8	140	140	0	0	0	0	0	0	0
27	30	3	27	27	0	0	0	0	0	0	0
60	60	0	90	99	9	0	0	0	33	33	0
133	133	0	141	141	0	0	0	0	12	12	0
0	0	0	30	30	0	30	30	0	0	0	0
200	200	0	192	192	0	0	0	0	172	172	0
123	123	0	236	236	0	0	0	0	0	0	0
87	104	17	84	84	0	0	0	0	18	18	0
200	200	0	400	400	0	400	400	0	0	0	0
0	0	0	169	169	0	169	180	11	0	0	0
60	60	0	60	60	0	0	0	0	0	0	0
70	70	0	77	84	7	0	0	0	0	0	0
70	53	-17	48	48	0	0	0	0	0	0	0
87	87	0	52	52	0	0	0	0	0	0	0
147	147	0	180	180	0	0	0	0	0	0	0
60	60	0	72	72	0	0	0	0	0	0	0
110	110	0	120	136	16	96	96	0	16	16	0
114	114	0	55	55	0	0	0	0	0	0	0
33	33	0	28	28	0	0	0	0	0	0	0
60	53	-7	50	50	0	0	0	0	0	0	0
34	34	0	20	20	0	0	0	0	0	0	0
67	67	0	30	30	0	0	0	0	0	0	0

Any help would be appreciated.

Thank you in advance,

Frankee Gee

 

[Joe4]

Perhaps you have the famous Excel floating arithmeric error (see: Floating-point arithmetic may give inaccurate result in Excel - Office)

Does this return the 8 you want?

=COUNTIFS(E3:E30:H3:H30:K3:K30:N3:N30,">=1")

 

[Fluff]

You cannot use ranges like that, this

=COUNTIFS(E3:E30:H3:H30:K3:K30:N3:N30,">0")

is the same as

=COUNTIFS(E3:N30,">0")

 

[Fluff]

How about

=SUMPRODUCT((E2:N2="Difference")*(E3:N30<>0))

 

[frankee_gee]

Perhaps you have the famous Excel floating arithmeric error (see: Floating-point arithmetic may give inaccurate result in Excel - Office)

Does this return the 8 you want?

=COUNTIFS(E3:E30:H3:H30:K3:K30:N3:N30,">=1")

Hey Joe4 - appreciate the reply. actually it returns the same number....88

 

[Joe4]

You cannot use ranges like that, this

=COUNTIFS(E3:E30:H3:H30:K3:K30:N3:N30,">0")

is the same as

=COUNTIFS(E3:N30,">0")

Ah, yes. I overlooked that range requirement (Monday Morning strikes again!).

To use COUNTIFS, you would need to do 4 separate COUNTIFS added together (or use the simpler SUMPRODUCT Fluff posted for you).

 

[frankee_gee]

You cannot use ranges like that, this

=COUNTIFS(E3:E30:H3:H30:K3:K30:N3:N30,">0")

is the same as

=COUNTIFS(E3:N30,">0")

Fluff reason I can't use that range is due to the fact it would include columns I don't want to count. thanks for the reply.

 

[frankee_gee]

How about

=SUMPRODUCT((E2:N2="Difference")*(E3:N30<>0))

Fluff - yeap.! this did it alright. the help is much appreciated. I can't recall you ever using this formula before. Thanks again Frankee.

 

[frankee_gee]

Ah, yes. I overlooked that range requirement (Monday Morning strikes again!).

To use COUNTIFS, you would need to do 4 separate COUNTIFS added together (or use the simpler SUMPRODUCT Fluff posted for you).

Joe4 - correct, this worked perfectly. thanks again!

 

[Fluff]

Fluff reason I can't use that range

But that's exactly what you are doing

Fluff - yeap.! this did it alright.

Glad we could help & thanks for the feedback.