how to do a count

[ammartino44]

Hello. I have values in Column D that I would like to count. I want to count all of the values of 2, BUT do not include the 2's for the value in column F (same row) is less than the value in column E (same row). How can I do this? Thanks.

[TMS]

Untested:

Please Login or Register  to view this content.

Regards, TMS

[ammartino44]

Thanks. I'll try this out. Is there a way to do it with countifs?

[Tony Valko]

Is there a way to do it with countifs?

Unfortunately, no.

[Vikas_Gautam]

Yeah it is possible with countifs..
Give it a try.. (Amazing use of countifs)

Please Login or Register  to view this content.

check the attached file..

[TMS]

@VG: I'm not sure that's what the OP meant. But 10/10 for effort.

I think, in this instance, the SUMPRODUCT is making the equivalent of an Array Function. So, maybe you could array enter the COUNTIFS without the SUMPRODUCT.

[Vikas_Gautam]

Thanks for appreciation..TMS..
I means a lot to me..

However Entering countifs using array will return the array of 1s and 0s..
So we have to use Sum with array or Sumproduct to sum the array...

I hope this helps..

A reputation point will encourage me TMS..

Thanks in advance..

[TMS]

Not sure of the value of using two functions where one will do but, as you say, amazing use of COUNTIFS.

[Vikas_Gautam]

Difference is in the Array Multiplication and Countifs
I guess the countifs version will be more fast with bigger ranges...
However you can test the speed.. using following link..
http://msdn.microsoft.com/en-us/libr...ffice.14).aspx
Use the code in the link to test the speed over bigger ranges to test the speed..
and declare the winner..
though I haven't tested it yet..

[TMS]

Thanks, I'll have a play with that at some point.

However, for the purpose of this thread, I'm prepared to take the simplistic view that, if SUMPRODUCT provides a result on its own, then it is likely that using SUMPRODUCT with COUNTIFS will take longer.

I think we need to wait for some input and.or feedback from the OP.

Regards, TMS

[Vikas_Gautam]

There is one more point of view...
The FORMULA AUDITING STEPS..
Just count the steps for both the formulas .. and which would have least steps will be the winner (I suppose)

Edit:
Countifs do it in 3-4 steps
Sumproduct with array multiplication will do it in 5-6 steps..

[TMS]

@VG: my original formula was marked as "untested". That's because I had no data to test it on and, as far as I am aware, I still don't. I know that you provided a workbook but that's your interpretation (which may well be correct). However, I've put my formula into that and compared it to yours.

Using the Range Timer code, it appears my formula is the faster of the two. But, using Evaluate Formula, there are less steps in yours, which I guess is a good thing.

All that said, I'm going to draw a line now as the OP doesn't seem to be showing any interest in the answers or the discussion.

Regards, TMS

[Tony Valko]

Yeah it is possible with countifs..

Not without passing the COUNTIFS to a function that can handle arrays. It is not possible using just COUNTIFS.

Using COUNTIFS as you have is very inefficient as I've noted in other threads.

[Vikas_Gautam]

Thanks TMS and Tony Volko..
Good discussion ends with a nice conclusions....

[Tony Valko]

Using COUNTIFS as you have is very inefficient...

Let me rephrase that.

Using COUNTIFS in that manner is slower to calculate. The larger the range the slower it is to calculate.

[Tony Valko]

@VG: my original formula was marked as "untested". That's because I had no data to test it on and, as far as I am aware, I still don't. I know that you provided a workbook but that's your interpretation (which may well be correct). However, I've put my formula into that and compared it to yours.

Using the Range Timer code, it appears my formula is the faster of the two. But, using Evaluate Formula, there are less steps in yours, which I guess is a good thing.

All that said, I'm going to draw a line now as the OP doesn't seem to be showing any interest in the answers or the discussion.

Regards, TMS

Related...

https://www.excelforum.com/showthread.php?t=1035202

[Vikas_Gautam]

Yeah.. Tony I got it..
you are absolutely correct..
I will take care in future while making formulas...

Can you tell me which is faster of the two...
1. NESTED IFS or
2. ARRAY MULTIPLICATION... ?

[Tony Valko]

In Excel 2002 I tested these array formulas on various sized ranges:

Formula1:

=INDEX(D:D,MATCH(1,(Range1=F2)*(Range2=G2)*(Range3=H2),0))

Formula2:

=INDEX(D:D,MATCH(1,IF(Range1=F2,IF(Range2=G2,IF(Range3=H2,1))),0))

Here are the results I got:

Data Range

	F	G	H	I	J	K	L	M
4	Formula1	Rows	Test1	Test2	Test3	Test4	Test5	Average
5		100	0.00025	0.00026	0.00026	0.00026	0.00026	0.000258
6		1000	0.00134	0.00133	0.00133	0.00133	0.00134	0.001334
7		10000	0.01151	0.01141	0.01141	0.01145	0.01141	0.011438
8		50000	0.03552	0.04889	0.04880	0.03633	0.04233	0.042374
9
10	Formula2	Rows	Test1	Test2	Test3	Test4	Test5	Average
11		100	0.00027	0.00027	0.00028	0.00027	0.00027	0.000272
12		1000	0.00139	0.00139	0.00145	0.00149	0.00139	0.001422
13		10000	0.01191	0.01193	0.01204	0.01188	0.01187	0.011926
14		50000	0.04399	0.04835	0.04158	0.04417	0.05409	0.046436

In this application array multiplication was slightly faster than nested IFs.

[Vikas_Gautam]

Thanks for the results Tony..
Now I will suggest Array multiplication.. as possible as I can..

Thank for this beautiful presentation as well..

[Tony Valko]

You're welcome!