Compare & sum previous month production by workday.


C

Cindy

I need a formula to compare the pound production of the current month, by
workdays, with the previous month. I have turned sumif and countif every way
I can and still can't get it to give me right total pounds.

To complicate the problem, the spreadsheet has the days across the rows with
a column for hours next to each day and then a week to date total at the end
of each week.

I have Excel 2007.

Ex.
Monday 2/3 Tuesday 2/4 Wednesday 2/5 Thursday 2/6 Friday 2/7 WeektoDate
Pounds Hours Pounds Hours Pounds Hours Pounds Hours Pounds Hours Pounds

11111 6.5 22222 7.0 33333 7.0 44444 7.0 55555 6.5
166665

There is then a column with the Month to date total. I have added a column
after that and want it to show the matching workdays amounts. Meaning, if I
have only 13 days of production so far this month I want the total of the
first 13 days of production in the previous month. Both months are in the
same workbook.
 
Ad

Advertisements

J

Jacob Skaria

Try this macro, I have assumed you have the values in ColA. Please change as
required. If you are new to macros; set the Security level to low/medium in
(Tools|Macro|Security). 'Launch VBE using short-key Alt+F11. Insert a module
and paste the below code. Save. Get back to Workbook. Tools|Macro|Run Macro

Sub InsertBlanksAfter100()

Dim intTotal As Integer
Dim lngRow As Long
Dim lngLastRow As Long

lngLastRow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row

For lngRow = lngLastRow To 1 Step -1
If intTotal = 100 Then
ActiveSheet.Rows(lngRow + 1).Insert
intTotal = 0
End If
intTotal = intTotal + Range("A" & lngRow)
Next
End Sub

If this post helps click Yes
 
J

Jacob Skaria

Hi Cindy

Suppose you have the Sheets named Jan,Feb,Mar,Apr and you are currently
working on Apr. I assume you insert two columns every day for Pounds and
Hours just before the WeektoDate column/ If my assumption is correct you can
try the below

I assume we have 5 days represented from Col A to Col K. For the pound total
in the current sheet use the below formula in cell L3.
=SUMPRODUCT(--($A2:K2="Pounds"),--($A3:K3))

In M3 where you need to get the details from the previous sheet ie(Mar) use
the below formula
=SUMPRODUCT(--(Mar!$A2:K2="Pounds"),--(Mar!$A3:K3))

For having this formula rolling; leave 1 column blank and always insert a
column from col J.

If this post helps click Yes
 
R

Roger Govier

Hi Cindy

I'm not saying it is impossible to do what you want, but it would be very
complicated.
Unfortunately, you have confused the storage of data, with reporting. They
would be best on separate sheets.
All of your data for the year (or longer) should be held on one sheet in the
format
Date Pounds Hours
with a row for each day going down the sheet.

Then you should have a Report sheet which pulls across the data that you
want to see, relative to an Start date that you give at the top of the
sheet.

It would be quite easy to convert your existing data to the new layout with
a small amount of VBA.
If you are able to send me your workbook, I would be happy to convert it for
you and return.
To mail direct, send to
roger at technology4u dot co dot uk
Change the at and dots to make valid email address
 
C

Cindy

Jacob, Thanks for looking at this. This almost gives me what I want. I
need a criteria where it matches workdays. ie. On the 13th workday of April
I want it to give me the total pounds for the 1st 13 workdays of Mar.

By the way, this also solved another issue I was having elsewhere. I like
a twofer!

Cindy
 
Ad

Advertisements

C

Cindy

Jacob,

After playing with this off and on all day, I "think" I have worked it out.
I actually ended up using your formulas for the MTD with a subtraction from a
column that I added using a CountIF. It worked on my test spreadsheet and
tomorrow at work I will try it on the actual on. Wish me luck.

Thanks for your help!!
 
C

Cindy

Roger, thank you for the advice and the offer of help. Actually I do
understand the difference between storing data and reporting, I have dabbled
a bit in Access and wrote a small P.O./Maintenance program for our trucking
company. Unfortunately, this spreadsheet is a monster that my boss likes and
is not interested in changing, so...............LOL

Off topic, my husband, a programmer, has said the same thing as you for
years about this spreadsheet. When I read what you wrote the thought crossed
my mind to ask if you were my husband. He, of course, thought it was
hilarious!

Cindy
 
J

Jacob Skaria

Cindy, Happy to hear you made it; and more than happy that I could help you....

I like what you mentioned ""spreadsheet is a monster that my boss likes and
is not interested in changing"


If this post helps click Yes
 
Ad

Advertisements

R

Roger Govier

Hi Cindy

Sorry for blaming you for your boss's muddled thinking<bg>
Shame he is such an Ostrich - but, you have to work with what you've got!!!

Do you have your sheets always starting on a Monday, or do they start with
whatever is the first workday for that month?
If it is the first workday, then provided you have the Analysis Toolpak
loaded, Tools>Addins>Analysis Toolpak then you can calculate the current
Workday.

In a separate cell on your sheet enter the formula
=NETWORKDAYS(DATE(YEAR(TODAY()),MONTH(TODAY()),0),TODAY())-1
This will give the number of workdays to today, so the column number for
Pounds, would have to be less that or equal to that figure * 2

Amend your SP formula
=SUMPRODUCT(--(Mar!$A2:K2="Pounds"),--(Mar!$A3:K3),--(COLUMN($A3:K3)<=WD*2))
where WD represents the cell location where you have the Networkdays formula
entered.

The NetWorkday formula can be amended to include Holidays as well, if
required.
The general format is
=NETWORKDAYS(Startdate,enddate,holidays) where holidays is either named
range or a range like $X1:$X10, which contains a list of holiday dates.

P.S. I'm not your husband<bg>
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top