PDA

View Full Version : Solved: sumif/countif for 'older'



stanl
04-01-2012, 05:57 AM
I am trying to help a friend with a spreadsheet he is develop ing for car dealerships. Although I think it best he build a database then populate the Excel forms I agreed to help with some formulas. I have a problem in 1 area. I am populating # in stock and Average Days in Service (DIS) for each model year. Populating the number in stock for 'other' was a matter of subtracting everthing for 2012-2006 from the total Count. For each DIS I used Sumif/Countif for the Days in Stock for each model year. My question is "How to calculate the Average DIS for 'older'"?


2012 2011 2010 2009 2008 2007 2006 older
In stock 1 24 23 20 15 7 8 4
DIS 14 23 24 20 25 23 19

Bob Phillips
04-01-2012, 06:45 AM
Older than 2006?

Do you have the data for each vehicle, I think it would be calculated from there in a similar (not same) manner as you did for each year.

stanl
04-01-2012, 07:38 AM
Yes, the data reads from another tab (see jpeg). I created range names and set up a hidden lookup cell for each year (ie 2012 = 12). So my formula for the DIS row (previous jpeg) for 2012 would be

=SUMIF(Year,B12,DAYS)/COUNTIF(Year,B12) where Year is Col B and Days = Col O.

So the 'older' cell would be the Average of vehicles with values in Col B like '05' '03' or '99'... so you can perhaps see where that presents a problem. If this were in a database I could use SQL "NOT IN" or "NOT BETWEEN" and get the correct results.

There may actually be an easy solution and I'm just missing the logic. Appeciate your looking at it though.

p45cal
04-01-2012, 08:48 AM
Consider a pivot table for most of your summary/stats.
Below, from a reduced data table:
7771
in the next message, the file is attached.
Perhaps if you were to attach a file sample yourself, I could try to do it with your data.

p45cal
04-01-2012, 08:50 AM
The file is attached.

stanl
04-01-2012, 09:05 AM
Consider a pivot table for most of your summary/stats.
Below, from a reduced data table:
7771
in the next message, the file is attached.
Perhaps if you were to attach a file sample yourself, I could try to do it with your data.

Thanks for your input, but it isn't ME that needs the help it is my friend and he wants the cell values filled in. If it were ME, I wouldn't be asking in the first place and either use a Pivot table or a DB.

Would be happy to tell him to consider a Plan-B if filling the 'older' cell is not possible via formula.

Bob Phillips
04-01-2012, 09:50 AM
Stan,

Just use

=SUMIF(Year,"<2006",DAYS)/COUNTIF(Year,"<2006")

stanl
04-01-2012, 12:25 PM
Stan,

Just use

=SUMIF(Year,"<2006",DAYS)/COUNTIF(Year,"<2006")

I was thinking you might say that, and it was the first thing I tried - result is #DIV/0 error, but if you look at the original jpeg there are 4 vehicles that meet the criteria. [EDIT] and they all have DIS>0

Bob Phillips
04-02-2012, 12:38 AM
Stan, I think we would need to see the workbook to see why that is happening. Any chance of that?

stanl
04-02-2012, 03:49 AM
Stan, I think we would need to see the workbook to see why that is happening. Any chance of that?

Might not have solved, but I fixed it. The model years were stored on 2 separate tabs, one would have the year as 2006, the other 06 - when I applied

=SUMIF(Year,"<2006",StartCost)/COUNTIF(Year,"<2006") to the tab with the full year it worked, if I used =SUMIF(Year,"<06",StartCost)/COUNTIF(Year,"<06") - got the error. I changed the 2-digit years to 4 digit and all is well.

My bad for not using the 2-digit year in my original post. The original workbook is quite clumsy, just a lot of stuff thrown in. If I get time I will study more the difference between applying the formula to 2 vs 3 digit year.

Bob Phillips
04-02-2012, 03:59 AM
You could use wildcards

=SUMIF(Year,"<*06",StartCost)/COUNTIF(Year,"<*06")

although correcting the data is probably better :)

stanl
04-02-2012, 05:40 AM
You could use wildcards

=SUMIF(Year,"<*06",StartCost)/COUNTIF(Year,"<*06")

although correcting the data is probably better :)

Are you sure about Wildcards. see attached