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

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

Status
Not open for further replies.

Frncis

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

vletm

Excel Ninja
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?
 

Frncis

Member
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?
I have used countifs before, this is the first time that I have been stumped.
 

Frncis

Member
Frncis
Show me,
how have You used that before?
It should use as I highlited with green to count...
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?
 

Peter Bartholomew

Well-Known Member
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

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

View attachment 58322

I would be nice if you could demonstrate what it is you are hoping to achieve.
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
 

vletm

Excel Ninja
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.
 

Frncis

Member
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.
I am going to play with your sample & get back to you. Yes that is what I am trying get.
 

Frncis

Member
Frncis
You could check this file too ... not only snapshot
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.
 

vletm

Excel Ninja
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?
 

Frncis

Member
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?
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?
I played around with pivot tables . What the screen shot you provided a pivot table?
 

vletm

Excel Ninja
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?
 

Frncis

Member
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?
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.
 

Frncis

Member
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.
To filter for the month I added this to the array I7,"=10",. It goes after COUNTIFS & the ( is replaced with ,
 

bosco_yip

Excel Ninja
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
 
Status
Not open for further replies.
Top