• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User


  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

How to Count


Excel Ninja
Deepak Sharma
If forget rows which E-column values are over 150,
there will be four rows left.
Those four rows are different, then 'count' would be 0.
What would be Your needed result with those values?
Hi vletm, Thanks for your time.

I mean actualy If Product Name, Thickness, Length and Witdth are same AND Pcs are less than 150 then I need to count that row with rest of the rows where Pcs are less than 150.

I think VBA can work in this way Because what we can do is :-
It should store the row from A1 to D1 then search for the same row in down rows/data. If finds the same then Check the Qty. of Pcs, If falls less then 150 then store the count...then same proceedure for other rows.
Thank you so much Peter...This is exactly I was looking for.....But how this is working with the column heads only ? If you could clear a bit.

Thank you vletm and Marc for your support too.

Last edited by a moderator:

Peter Bartholomew

Well-Known Member
@Deepak Sharma

I converted the simple data range to an Excel Table. As far as I am concerned the introduction of tables renders the original spreadsheet concept of direct referencing (A1 etc.) unnecessary and, thankfully, obsolete. The thing to Google is 'Structured Referencing'.

As you observed, a column of data is referenced by name using the heading enclosed in square brackets. The only form of relative referencing is introduced by the @ symbol which identifies the fields within a given record.

It is worth looking up descriptions of Tables and giving some time to their study since they are the basis of new functionality such as Power Query and Power Pivot.

Peter Bartholomew

Well-Known Member
The introduction of tables renders the original spreadsheet concept of direct referencing unnecessary and, thankfully, obsolete.
I ought to apologise for that statement. I was in a rush to get onto a WebEx meeting so was not as tactful as I might have been.

It may be my opinion but I appreciate that most would disagree and could justify contrary opinions.
I am not sure I understand the problem or your intended output. How far does the attached workbook come to meeting your specification?
Hello Everyone & Peter,

I don't whether I should write in this query or not because this is related to this one only. I just need to add one more condition to it, thats why thought to ask here.

What I need is to countifs Column A, B, C & D are same and Col E should be <= 150 & >200.

Please see if this is possible. I tried but not giving accurate result.

And also Can I get results in cells like: "1 <150 and 2 >200"
Because with the total figure it would again confusing that how many less than and how many more than.


Peter Bartholomew

Well-Known Member
Hi Deepak

Because of the manner in which COUNTIFS etc. are optimised for performance, it is not possible to perform calculations on the criteria ranges; the function's parameters must be simple range references. You can either use helper fields or revert to the somewhat slower array formulas for the calculation, in this case contained within the SUMPRODUCT() function.
Pivot tables might also offer a way forward.