1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. 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

    Hui...

  3. 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

Discussion in 'Ask an Excel Question' started by Deepak Sharma, Aug 9, 2018.

  1. Deepak Sharma

    Deepak Sharma Member

    Messages:
    164
    Hi All,
    I need to countif Column A, B, C & D are same and Col E should be <= 150.

    Please see attached.



    Thanks and Regards,

    Attached Files:

  2. vletm

    vletm Excel Ninja

    Messages:
    4,137
    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.
    Or
    What would be Your needed result with those values?
    Deepak Sharma likes this.
  3. Deepak Sharma

    Deepak Sharma Member

    Messages:
    164
    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.
  4. Marc L

    Marc L Excel Ninja

    Messages:
    4,253
    Hi !
    As again you didn't create the thread in the appropriate VBA section (‼)
    let's wait if some Excel formula helper can propose a formula way
    as here it's the Excel formula section …
  5. vletm

    vletm Excel Ninja

    Messages:
    4,137
    Deepak Sharma
    Did You answer to my question?
    ... how many countif results would be there?
    ... are all zero?
    Last edited: Aug 9, 2018
  6. Peter Bartholomew

    Peter Bartholomew Active Member

    Messages:
    413
    I am not sure I understand the problem or your intended output. How far does the attached workbook come to meeting your specification?

    Attached Files:

  7. Deepak Sharma

    Deepak Sharma Member

    Messages:
    164
    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.

    Regards,
    Last edited by a moderator: Aug 9, 2018
  8. Peter Bartholomew

    Peter Bartholomew Active Member

    Messages:
    413
    @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.
    Deepak Sharma likes this.
  9. Peter Bartholomew

    Peter Bartholomew Active Member

    Messages:
    413
    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.
  10. Deepak Sharma

    Deepak Sharma Member

    Messages:
    164
    :)Thank you Peter....Thanks for all this deep explanations buddy
  11. Deepak Sharma

    Deepak Sharma Member

    Messages:
    164
    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.

    Regards,
  12. Deepak Sharma

    Deepak Sharma Member

    Messages:
    164
    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.

    Regards,
  13. Peter Bartholomew

    Peter Bartholomew Active Member

    Messages:
    413
    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.

    Attached Files:

  14. Deepak Sharma

    Deepak Sharma Member

    Messages:
    164
    Allrite ! Thanks Peter....This was also helpful. Thank you so much!

    Regards,

Share This Page