Counting action under condition of theyr value, and month when..

Solved/Closed
Branok Posts 7 Registration date Friday September 11, 2015 Status Member Last seen October 9, 2015 - Oct 8, 2015 at 08:06 AM
Mazzaropi Posts 1965 Registration date Monday August 16, 2010 Status Contributor Last seen September 18, 2022 - Oct 9, 2015 at 11:41 AM
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

Mazzaropi Posts 1965 Registration date Monday August 16, 2010 Status Contributor Last seen September 18, 2022 146
Oct 8, 2015 at 10:54 AM
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
0
TrowaD Posts 2900 Registration date Sunday September 12, 2010 Status Moderator Last seen September 12, 2022 523
Oct 8, 2015 at 11:14 AM
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
0
Branok Posts 7 Registration date Friday September 11, 2015 Status Member Last seen October 9, 2015
Oct 9, 2015 at 02:49 AM
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
0
Mazzaropi Posts 1965 Registration date Monday August 16, 2010 Status Contributor Last seen September 18, 2022 146
Oct 9, 2015 at 11:41 AM
You're welcome!

I feel happy to help you.

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