Simple arithmetic nested in a Countifs formula - is it possible?

[spydey]

Love posting on this forum due to the assistance, explanation, and time that others put into helps us noobs. So I thank you in advance.

Ok, so I need to find the percentage of some data that I have.

The simple equation would be:

[CENTER]Total Number of Events w/in 30 days of Date1 while Date2 is between 2 dates
--------------------------------------
Total Number of Events[/CENTER]

Simple math, right?

Getting it to equate to an excel formula is causing me a bit of headache due to the criteria that I need to use.

Regarding the Top Section of the equation:

The top section should follow this logic:

• When ITEM = $A2 and LOCATION = $B2 ('$' intentionally left off of the row number)
• Count the total number of events, where # days between Date2 & Date1 is <= 30
• Date 2 must be between or equal to 2 separate dates, normally the 1st of the month (Starting Date) and the last day of the month (Ending Date)

Note, Date1 will always proceed Date2 chronologically.

Ex: Date 1 = 07/01/17, Date 2 = 09/05/17, Starting Date = 09/01/17, Ending Date = 09/30/17, this would produce a count of 0 (zero) because the number of days between Date 1 & Date 2, while Date 2 is >=09/01/17 & <=09/30/17, is greater than 30.

Ex: If we use the same data from the above example, but change the "Date 1" to 08/15/17, then we would have a count of 1.

I can't seem to produce a formula using the above criteria to give me the expected output for the Top Section of the equation.

I have tried the following for the Top Section:

Note that cell A1 contains the Starting Date, i.e. 09/01/2017; ITEM, LOCATION, DATE1, & DATE2, are all individual ranges of cells, each a separate column, that I designated as Named Lists for ease of use in formulas.

=COUNTIFS(ITEM,$A2,LOCATION,$B2,DATE2,">="&$A$1,Date2,"<="&EOMONTH($A$1,0),(DATE2-DATE1),"<="&30)

I believe that my error lies in the simple arithmetic nested in the COUNTIFS formula; I have bolded it in the above formula.

----------

Regarding the Bottom Section:

I can get the Bottom Section of the equation just fine and I have tried to adapt it to include the required 30 day period for the Top Section, but I don't get the expected output.

The Bottom Section is as follows:

=COUNTIFS(ITEM,$A2,LOCATION,$B2,DATE2,">="&$A$1,DATE2,"<="&EOMONTH($A$1,0))

That formula works as expected and produces the correct count for all events per ITEM, per LOCATION within the specified date range.

---------

If I could just get the top section to work correctly, I could: =Top Section/Bottom Section, to get a ratio and easily convert that into a percentage.

But the Top Section formula is giving me a headache.

The error I am getting is:

We found a problem with this formula. Try clicking Insert Function on the Formulas tab to fix it, or click Help for more info on common formula problems. Etc etc etc ........

I honestly think that I am getting an error due to the (DATE2-DATE1) math I am trying to perform in the Countifs formula. I just don't know how to get around it/fix it.

Any pointers are greatly appreciated!!

Thanks a million and have a great day, afternoon, evening, night, wherever you are!

-Spydey

 

[Jonmo1]

I honestly think that I am getting an error due to the (DATE2-DATE1) math I am trying to perform in the Countifs formula.

Yes, that is why it doesn't work.
Countifs (all the **IFs family of functions) cannot manipulate the ranges they evaluate.
It can only look at the values exactly as they exist in the ranges.

I would suggest a helper column to do the Date2-Date1, then test that column in the countifs.

 

[spydey]

Hey there Jonmo1!! Thanks for the input. I had thought about creating basically a helper column were I pre-calculate the number of days between Date2 & Date1, but I was hoping I could accomplish the same calculation in my formula and save me the trouble of creating yet another column. Thanks for the clarification. I guess that I will have to create a helper column.

Thanks again for your time and assistance.

Take care!

-Spydey

 

[barry houdini]

Hello Spydey,

You can’t do that sort of arithmetic in COUNTIFS. if you use an additional column for the date subtraction you can retain that approach but I’d suggest an array formula which avoids repetition, i.e..for the whole calculation

=AVERAGE(IF(ITEM=$A2,IF(LOCATION=$B2,IF(DATE2>=$A$1,IF(Date2<=EOMONTH($A$1,0),IF(DATE2-DATE1<=30,1,0))))))

confirm with CTRL+SHIFT+ENTER

 

[spydey]

Barry,

Thanks man. I have used arrays before but hadn't thought about it with this particular scenario.

However, why would we use the AVERAGE function to give me a count?

-Spydey

 

[barry houdini]

However, why would we use the AVERAGE function to give me a count?

It's a percentage isn't it? You are dividing the number of events that meet some criteria by the total number of events that meet that criteria except the date difference, so perhaps that would give you a calculation like 3/10 = 30%

In my suggested formula all the events that satisfy all criteria are assigned 1, the events that meet all criteria except date difference are assigned 0, so for my example you get

1,1,1,0,0,0,0,0,0,0

....and when you average those values you get the same percentage 30%

 

[spydey]

Barry,

Yes, you are correct, it is a percentage. I understand now what your formula is doing. It is quite ingenious! Thank you for the explanation.

-Spydey

 

[spydey]

Hello Spydey,

You can’t do that sort of arithmetic in COUNTIFS. if you use an additional column for the date subtraction you can retain that approach but I’d suggest an array formula which avoids repetition, i.e..for the whole calculation

=AVERAGE(IF(ITEM=$A2,IF(LOCATION=$B2,IF(DATE2>=$A$1,IF(Date2<=EOMONTH($A$1,0),IF(DATE2-DATE1<=30,1,0))))))

confirm with CTRL+SHIFT+ENTER

Barry,

So I gave your formula a try. It was reporting 100% for all Items, when there should have been a different spread across all items. Also, I was getting a #DIV/0! error on some of them because there wasn't a single item in some locations, where DATE2 was between the starting and ending date. So I edited your formula a bit to indicate that if the starting date and ending date did not match the needed date, to make it a 0. By doing this, it alleviated my DIV/0 error and correctly reported/calculated the percentage.

Here is what I did:

=AVERAGE(IF(ITEMS=$A2,IF(LOCATION=$B2,IF(DATE2>=$A$1,IF(DATE2<=EOMONTH($A$1,0),IF(DATE2-DATE1<=30,1,0),0),0))))

Also, I inserted that equation as an array via CSE.

Just thought I would let you know! :D

Thanks again for you help and input.

-Spydey