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

#### vletm

##### Excel Ninja
Frncis
Show me,
how have You used that before?
It should use as I highlited with green to count...

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

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
Frncis
So, You would like to get those below values?

(... without any formulas, no need to copy paste ... )

#### Frncis

##### Member
Frncis
So, You would like to get those below values?
View attachment 58334
(... without any formulas, no need to copy paste ... )
This may sound like a dumb question. You set it up as a table, & you can still chart the amounts?

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

#### vletm

##### Excel Ninja
Frncis
You could check this file too ... not only snapshot

#### Attachments

• 81.2 KB Views: 4

#### Frncis

##### Member
Frncis
You could check this file too ... not only snapshot
Thanks for screen shot. I will look closer further tomorrow, but that just may work.

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

#### Frncis

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

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

#### vletm

##### Excel Ninja
Frncis
A short version ... hmm?
and You'll use that about 92000 times ...
Good luck!

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