Counting action under condition of theyr value, and month when.. [Solved/Closed]

Report
Posts
7
Registration date
Friday September 11, 2015
Status
Member
Last seen
October 9, 2015
-
Mazzaropi
Posts
1833
Registration date
Monday August 16, 2010
Status
Contributor
Last seen
May 30, 2018
-
Hello Guys,
I came here for help again :)

I need to solve one issue. I've created database, which contain column with Dates(column E),and column with Action's duration in days(column F)[This column also contains blank cells or cells with "NFF" because actions didn't actually append or other reasons...]. I've been trying for long but cannot come with any formula, which will count Actions(column F) occurred in ONE-certain month , moreover, i wish only those values be counted which duration is equal 2 or less. In the end, I expect outcome in column J, where I want have amount of actions (finished within 2days) for each month.

I prepared simple example, accordingly to description.
Link: http://speedy.sh/YWNwj/Example.xlsx

I need formula for column J - for each month, cells are marked with red question marks.

PS: Formula array should probably include all the dates and values, because values and dates can change and will be added by users, so formula have to work independently from entries order or so.

Thank you in advance, and looking forward for some answers.

4 replies

Posts
1833
Registration date
Monday August 16, 2010
Status
Contributor
Last seen
May 30, 2018
135
Branok, Good morning.

Try to use:

J3 -->
=SUMPRODUCT(--ISNUMBER($E$3:$E$49),--(MONTH($E$3:$E$49)=ROW(A1)),--(($F$3:$F$49)>0),--(($F$3:$F$49)<=2))

Copy it down.

http://speedy.sh/jPrsd/08-10-2015-en-Kioskea-Counting-with-Criteria-OK.xlsx

Is this what you're looking for?
I hope it helps.
--
Belo Horizonte, Brasil.
Marcílio Lobão
Posts
2599
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
March 24, 2020
401
Hi Branok,

If you result list in column J should look like this ...


... then I understood you correctly and you can use the following array formula:
=SUM((MONTH($E$3:$E$29)=1)*(($F$3:$F$29=1)+($F$3:$F$29=2)))

Array formula's need to be confirmed by hitting Ctrl+Shift+Enter, if done correctly the formula will be enclosed by curly brackets {}.

This is the formula for January in J3. For February in J4 change the underlined 1 with a 2. You can also put these numbers in the next column and refer to them in your formula for easy dragging.

Best regards,
Trowa
Posts
7
Registration date
Friday September 11, 2015
Status
Member
Last seen
October 9, 2015

Guys,
I am sorry but i probably was not clear enough, excuse my English please. I need to get number of actions, which have been done in two days, which mean 0,1 and finally 2 in column F. I am attaching another link with same example,but now it contains written results which should be outcome of the formula. Thank you for patience :)

Link:http://speedy.sh/YWt6j/08-10-2015-en-Kioskea-Counting-with-Criteria.xlsx
Posts
1833
Registration date
Monday August 16, 2010
Status
Contributor
Last seen
May 30, 2018
135
You're welcome!

I feel happy to help you.

Have a nice day!
--
Belo Horizonte, Brasil.
Marcílio Lobão