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.

Counting variable quantity in a cell range, during a month.

Discussion in 'Ask an Excel Question' started by Frncis, Feb 25, 2019.

Thread Status:
Not open for further replies.
  1. Frncis

    Frncis Member

    Messages:
    86
    I have a spread sheet that breaks down data by month. I.e. column I is the month, & Column j is the size. So, cell I7 has (10), & cell J7 has a quantity (5). The header for column I7 is Month, J7 is 6M. I8 may have 10 as the month, but the quantity in J8 is 15.

    I have another area on the same sheet tracks all the sizes for the month of October. Cell AI7 has this formula =COUNTIFS(J7,"1",I7,10). It is a formula that I have used for another application, & I am trying to modify. The expected answer should be 5, but I can’t figure out the correct formula. I have googled, but have not been successful. I guess, my question is how do I replace "1" with something that accepts a variable answer.
  2. vletm

    vletm Excel Ninja

    Messages:
    4,918
    Frncis
    hmm?
    Have You read or try to find out what would COUNTIFS 'do'?
    Counts the number of cells within a range that meet multiple criteria.
    >> You should use something another function to get Your '5'.
    >> Count ... and ... You have there two possible cells ... never 5 <<
    >> and someway You would like to get a value from J7 <<
    Or what?
  3. Frncis

    Frncis Member

    Messages:
    86
    I have used countifs before, this is the first time that I have been stumped.
  4. vletm

    vletm Excel Ninja

    Messages:
    4,918
    Frncis
    Show me,
    how have You used that before?
    It should use as I highlited with green to count...
  5. Frncis

    Frncis Member

    Messages:
    86
    In another workbook the following formula is used =COUNTIFS(AF4:AF4,"1",AB4:AB4,10) The "1" is linked to another cell that counts the nunber of times a yes response is entered. I see part of the problem. The formula I used previously was for a static value. NOW how do you account for a dynamic value?
  6. vletm

    vletm Excel Ninja

    Messages:
    4,918
  7. Peter Bartholomew

    Peter Bartholomew Well-Known Member

    Messages:
    709
    If I put an array of numbers in place of your "1"
    = COUNTIFS(_6M,{5;6;7;8;9;10;11;12;13;14;15;16;17;18},Month,10)
    then I would get a column of counts of each "6M" value for the 10th month.
    In the case you have described there is one 5 and one 15; everything else is zero. As an alternative to using an array constant, you could use a range such as that shown in column N containing the possible values to look for.

    upload_2019-2-25_23-11-31.png

    I would be nice if you could demonstrate what it is you are hoping to achieve.
    Frncis likes this.
  8. Frncis

    Frncis Member

    Messages:
    86
    I am trying to track the number of each size shoe that is provided each month.
    I have included a link to a sample. Look at the month of October.
    https://1drv.ms/x/s!Ak-4iXjPpsJMgRSCGsAOi2cjYm8z
  9. vletm

    vletm Excel Ninja

    Messages:
    4,918
    Frncis
    So, You would like to get those below values?
    Screenshot 2019-02-26 at 16.12.05.png
    (... without any formulas, no need to copy paste ... )
    Frncis likes this.
  10. Frncis

    Frncis Member

    Messages:
    86
    This may sound like a dumb question. You set it up as a table, & you can still chart the amounts?
  11. vletm

    vletm Excel Ninja

    Messages:
    4,918
    Questions are never dumb...
    That was a quick sample.
    1st I deleted all column from F as far as there were something.
    I didn't set it up as a table ... but it would be good to do if ...
    Of course, a chart is possible to do too.
  12. Frncis

    Frncis Member

    Messages:
    86
    I am going to play with your sample & get back to you. Yes that is what I am trying get.
  13. vletm

    vletm Excel Ninja

    Messages:
    4,918
    Frncis
    You could check this file too ... not only snapshot

    Attached Files:

  14. Frncis

    Frncis Member

    Messages:
    86
    Thanks for screen shot. I will look closer further tomorrow, but that just may work.
  15. Frncis

    Frncis Member

    Messages:
    86
    If you look at the sample I provided to the left of October, you will see the raw data. There will be multiple Oct, Nov., etc. So I have to capture the month, size & quantity provided. I chart the information from the above step. I am currently trying various formulas. Thy all give a 0 answer, the correct answer for the first entry is 5.
  16. Frncis

    Frncis Member

    Messages:
    86
    I have a formula that works if the quantity is static. =COUNTIFS(K7,"4",I7,10)*K7 Is there a way to have a range of numbers from 1 - 100, in the above formula.
  17. vletm

    vletm Excel Ninja

    Messages:
    4,918
    Frncis
    Why would You like to use many many same formulas,
    if You could get those from Pivot-table?
    Can You give exact clear sample results,
    what are You looking for?
    and
    what is missing?
  18. Frncis

    Frncis Member

    Messages:
    86
    I played around with pivot tables . What the screen shot you provided a pivot table?
  19. vletm

    vletm Excel Ninja

    Messages:
    4,918
    Frncis
    I sent screenshot with #9 reply
    and
    FILE with #13,
    which is a copy from Your 'bigger file's Shoes Provided-sheet.
    Which I cleaned as I wrote with #11 reply.
    What did You try to ask?
  20. Frncis

    Frncis Member

    Messages:
    86
    Problem solved. I had used an array elsewhere in the workbook, & didn't know what ii was. Here is a short version of the solution.
    =SUM(COUNTIFS(K7,{"1","2","3","4","5","6","7","8","9","10","11","12","13","14","15","16","17","18","19","20","21","22","23","24","25","26","27"}))*E7
    I want to say thank you for exposing me to Pivot Tables. I will spend more time with them. Also for pointing me in the right direction for possible answers.
  21. vletm

    vletm Excel Ninja

    Messages:
    4,918
    Frncis
    A short version ... hmm?
    and You'll use that about 92000 times ...
    Good luck!
  22. Frncis

    Frncis Member

    Messages:
    86
    To filter for the month I added this to the array I7,"=10",. It goes after COUNTIFS & the ( is replaced with ,
  23. bosco_yip

    bosco_yip Excel Ninja

    Messages:
    2,191
    1] Despite our numerous asking (post #7, #17 & #19), the OP still don't telling us what do he want to achieve.

    2] In order not to waste our reader's time, this post closed.

    Regards
    Bosco
Thread Status:
Not open for further replies.

Share This Page