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.

Help with Formula Required

Discussion in 'Ask an Excel Question' started by clramjit, Oct 16, 2018.

  1. clramjit

    clramjit New Member

    Messages:
    12
    Hi,

    I have attached a file with an example of what I'm looking for.
    I am trying to get a formula to tally a no. of cells yet omitting a cell count if a certain date condition is met from a referenced cell. It is hard to explain here so a better explanation is contained withing the excel sheet example itself.

    Thanks

    CLRamjit

    Attached Files:

  2. Hui

    Hui Excel Ninja Staff Member

    Messages:
    11,668
    E18: =COUNTIFS(E$8:E$16,1,$B$8:$B$16,">0")
    E19: =COUNTIFS(E$8:E$16,1,$C$8:$C$16,">0")
    E20: =COUNTIFS(E$8:E$16,1,$D$8:$D$16,">0")
    copy all across

    or

    E18: =SUMPRODUCT(($B$8:$D$16>0)*($B$6:$D$6=RIGHT($B18,2))*(E$8:E$16=1))
    copy down and cross
    Thomas Kuriakose likes this.
  3. clramjit

    clramjit New Member

    Messages:
    12
    Hui, thanks for your quick response, however, it's not quite what I'm looking for. Let me explain:

    I want E18 to indicate the "1"s that are present on that particular day who are Trained from Col B minus who are "expired" on Col B. So it should be like this {this is the current formula on sheet}
    =SUMPRODUCT((E7:E16={"OT",1})*($B$7:$B$16>=DATEVALUE("1/1/2018") [omitting the "1"s that are expired] The bold text is what I don't know how to represent as a formula together with existing formula.
    Simple terms I want to represent all that is in current formula subtracting the No. that has expired in Col B for that day(25th).

    Hope the explanation was somewhat comprehensible. I've reattached the sheet showing just the related data.

    Thank you again for any help and feedback.

    CLRamjit

    Attached Files:

  4. Peter Bartholomew

    Peter Bartholomew Well-Known Member

    Messages:
    500
    I think the problem was that the table date was 2017 which meant that all training was current.

    Attached Files:

  5. clramjit

    clramjit New Member

    Messages:
    12
    Thanks Peter,

    Your solution is working, but there is a twist to it now, where I would like to also tally in the same "assigned" range, cells that contain "OT" and in the "Training" range, cells that has "X". With this the hope is to capture some of the other parameters I'm trying to tally, daily. Hope it can be done.

    I'v re-attached the sheet with the additional requests.

    Thank you very much again for your input.

    You all are the Best!

    CL Ramjit

    Attached Files:

  6. pecoflyer

    pecoflyer Active Member

    Messages:
    274
    Any further requirements before you get help?
    Peter Bartholomew likes this.
  7. Peter Bartholomew

    Peter Bartholomew Well-Known Member

    Messages:
    500
    Do you really need to make your data so inconvenient to process? Now both criteria ranges have both numbers and text, each to be tested using an OR condition.

    I simplified the test for 1 or "OT" by merely testing for 'not blank', "<>". For the test on the date or "x", I created an array criterion
    = IF( {0,1}, ">"&Date, "x" )
    and then used SUM to aggregate the resulting counts.

    If anyone else has better ideas then, by all means, chip in; I will not be offended!

    Attached Files:

  8. clramjit

    clramjit New Member

    Messages:
    12
    Peter,

    Thanks for your help again. Sorry for the lengthy time before replying. I was trying out the solution. It does work on this sheet, however, I tried to use the formula in another sheet and for some reason it seems as though I'm missing something. Reminder I am not versed in excel formulas, I just can express what I want in plain English in the hope that can transform into a formula. Having said that, it is doing what I requested, but how can I use your formula on my real worksheet? Can you expand the hidden names/array please? This way I can try to transform it to the real file.

    Thanks Again.

    CLRamjit.

    Attached Files:

  9. Peter Bartholomew

    Peter Bartholomew Well-Known Member

    Messages:
    500
    clramjit
    Sorry to cause you grief. My basic building blocks are not the Excel defaults that almost everyone else uses. The key is to open up Name Manager on the Formulas ribbon tab.

    You will see that 'Training.R1' refers to the absolute range
    =TEST!$B$8:$B$16
    and, indeed, you could use the direct reference in place of the name.

    The personnel assignment is a little more complicated in that 'assigned' is a mixed reference, referring to the column directly above the formula. Note the dollar signs; their placement is important.
    =TEST!H$8:H$16

    Most obscure of all is 'criterion' that refers to a snippet of a formula
    = IF( {0,1}, ">"&Date, "x" )
    This evaluates to give a little text array
    {"x",">43460"}
    which forms the two alternative success criteria. When I use the 'criterion' in the COUNTIF formula, it will evaluate twice, once with the date comparison and the second time simply looking for an "x".

    I hope this at least moves you in the right direction to find information elsewhere.
  10. clramjit

    clramjit New Member

    Messages:
    12
    Peter, Thanks once again for your patience, I finally figured it out when transferred to my real sheet, at least the part of what you suggested. But in doing so I came across another hiccup. There are other text values that will be placed in the E8:E16 cells, namely "S", "V", "P", which counts certain situations. Is there a way to use exactly what you have given, {= SUM( COUNTIFS( assigned, "<>",Training.R3, criterion) )} and include "S", "V", "P" in the formula as values "not to be counted" if entered? I tried, but couldn't get it. I re-uploaded the sheet to show exactly what I'm talking about.
    Peter, I know you're probably fed up with this newbie, but the question is open to anyone in the Forum who can help.

    Thank you once again!

    CL.

    Attached Files:

  11. Peter Bartholomew

    Peter Bartholomew Well-Known Member

    Messages:
    500
    {= SUM( COUNTIFS( assigned, "<>",Training.R3, criterion) )}
    will accept any non-blank in the assigned column. At the other extreme,
    {= SUM( COUNTIFS( assigned, "1",Training.R3, criterion) )}
    will accept only 1. It will start to get a mess if you wish to treat different characters differently.

    COUNTIFS only accepts range references as parameters so if you need to apply a filtering formula first that would require a helper range to select the relevant personnel into the calculation.

Share This Page