Sum based on variable.... 2

[Steve4321]

I am a new user and have encountered a problem with a spreadsheet tracking productivity. I have tried several different ways to get the information I need but keep falling short when I try to sum using a "From" "To" date range. My spreadsheet is laid out basically as follows:
A B C D E F
Date Shop Prod1st Prod2nd Prod3rd ProdTotal

I have been able to get totals by shop and shift but when I try to look at just a certain months production nothing I've tried seems to work. Any help would be greatly appreciated. If necessary I can e-mail a part of the program showing formulas I am currently using.

Thanks in advance.

 

[SkipVought]

Steve,

What formula are you using?

Please post a sample of your data.

Skip,

_{[red]Be advised:[/red]When Viscounts were guillotined just as they were disclosing where their jewels were hidden, it shows to go that you should...
Never hatchet your Counts before they chicken!}

 

[bluedragon2]

Without seeing sample data, I think this may be what you are looking for:

=SUMPRODUCT((A1:A1000>from date)*(A1:A1000<to date)*(F1:F1000))

But, I bet a pivot table will be an option you may want to explore.

[Blue]Blue[/Blue] [Dragon]

If I wasn't Blue, I would just be a Dragon...

 

[Steve4321]

A B C D E F
Month Shop Prod1st Prod2nd Prod3rd ProdTotal
01/03/04 6140 750 675 0 1425
01/05/04 7210 450 475 500 1425
01/06/04 6140 675 700 0 1375
02/10/04 7210 525 550 475 1550
02/11/04 7210 500 475 525 1500
02/12/04 6140 700 650 0 1350
03/04/04 7210 550 475 550 1575
03/05/04 6140 725 650 0 1375
03/06/04 6140 750 725 0 1475

To get the total for all dates I have tried several different ways. The first was a long nested IF formula, then I used:
=SUMIF(B:B,"6140",C:C)
Also set up as database and used DSUM. I would like to stay away from a pivot table because other supervisors will be using this report and they do not understand pivot tables. I want them to be able to put in Shop, Shift, and date range to pull up the information.

Thank you both for your quick response.

 

[SkipVought]

[tt]
=SUMPRODUCT((Shop=L2)*(ProdTotal))
[/tt]
where column L has a list of unique Shops.

BTW, I used Named ranges.

How can I rename a table as it changes size faq68-1331

Skip,

_{[red]Be advised:[/red]When Viscounts were guillotined just as they were disclosing where their jewels were hidden, it shows to go that you should...
Never hatchet your Counts before they chicken!}

 

[KenWright]

Blue and Skip's formulas combined gives you date range and Shop specific totals (Assumes you have used named ranges as Skip suggested)

=SUMPRODUCT((Shop=L2)*(A1:A1000>from date)*(A1:A1000<to date)*(ProdTotal))

or

=SUMPRODUCT(--(Shop=L2),--(A1:A1000>from date),--(A1:A1000<to date),(ProdTotal))


Regards
Ken................

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

 

[Steve4321]

Ken,Skip and Blue;
Thank you for your help on this problem. I researched the SUMPRODUCT function and it is the one I am looking for. The problem that I am having now is that SUMPRODUCT is giving me a different fault depending on how I put it in. I think that this may have to do with my dynamic named ranges. Let me start with my range formula.
=OFFSET('Data'!$A$2,0,0,COUNTA('Data'!$A$2:$A$65536),1)
This formula is for the date column, each range is the same except the columns change.

=SUMPRODUCT((Shop=A2)*(Date>B2)*(Date<B3)*(ProdTot)) gives me #N/A.
=SUMPRODUCT(--(Shop=A2),--(Date>B2),--(Date<B3),(ProdTot)) gives me #VALUE!.

I know I must be close but just can't figure this out.

Thanks again for all your help!

 

[xlbo]

you have some #N/A errors in your data and some of your dates are held as text (the #VALUE! error is occuring because the -- is trying to convert to a value and it cannot)

Formulae are fine

Rgds, Geoff

A file that big? It might be very useful. But now it is gone

Please read FAQ222-2244 before you ask a question

 

[xlbo]

oh - and there may be an issue with your dynamic names - can happen if they are not the same length - this can be tested by subbing the names for the actual hard coded ranges...

Rgds, Geoff

A file that big? It might be very useful. But now it is gone

Please read FAQ222-2244 before you ask a question

 

[Steve4321]

Geoff,
Thanks for your input. I hard coded the ranges and the errors are gone but the answer it gives is 0. Again I made sure that the range lengths were the same and tried the formula both ways, ()*() and --(),--(). Will a 0 in some of the cells cause this problem? I double checked the formats and the dates are formated mm/dd/yy and all other columns are formated as numbers.

 

[xlbo]

ok - if you hard coded the ranges and it gives you 0 then your dynamic ranges are not working properly (probably different lengths). Easiest way to get around this is to always COUNTA on ONE column only eg

Range: Shop
Formula: =OFFSET('Data'!$A$2,0,0,COUNTA('Data'!$A$2:$A$65536),1)

Range: FromDate
Formula: =OFFSET('Data'!$B$2,0,0,COUNTA('Data'!$A$2:$A$65536),1)



Rgds, Geoff

A file that big? It might be very useful. But now it is gone

Please read FAQ222-2244 before you ask a question

 

[byundt]

With your sample data, I was getting 0 for the SUMPRODUCT because there were no dates between the two extremes. When I changed it to <= and >=, then I got non-zero results.

But the original question was looking to specify a month. So consider using:
=SUMPRODUCT((Shop=A2)*(MONTH(Date)=MONTH(B2))*(YEAR(Date)=YEAR(B2))*ProdTot)
This formula matches both the month and year.

If matching the year is not important (perhaps you only have data for one year), then you can use:
=SUMPRODUCT((Shop=A2)*(MONTH(Date)=MONTH(B2))*ProdTot)


These formulas will work as long as the Date column doesn't contain spaces or dashes. The dates may be either text (that looks like dates) or date/time serial numbers.

Brad

 

[Steve4321]

Brad,
I put in the formulas exactly as you have them and I am still getting a 0 for sum. These are the formulas that I've tried.
=SUMPRODUCT((Shop=A2)*(MONTH(Date)=MONTH(B2))*(YEAR(Date)=YEAR(B2))*ProdTot)
=SUMPRODUCT((Shop=A2)*(MONTH(Date)>=MONTH(B2))*(YEAR(Date)<=YEAR(B2))*ProdTot)

Whenever I get this to work I know I'll probably kick myself. Any help would be appreciated.
Steve

 

[byundt]

I noticed that you switched the columns for Date and Shop between the initial question and one of your later posts. Was this correct? The first formula below is if dates are in column B; the second is if dates are in column A:
=SUMPRODUCT((Shop=A2)*(MONTH(Date)=MONTH(B2))*(YEAR(Date)=YEAR(B2))*ProdTot)
=SUMPRODUCT((Shop=B2)*(MONTH(Date)=MONTH(A2))*(YEAR(Date)=YEAR(A2))*ProdTot)

 

[Steve4321]

To all,
Thank you everyone for your help. Turns out my problem was one column of data formated as text instead of numbers. Spreadsheet works great.

Steve

 

[Steve4321]

One more question, is there a faster way to get the same results? I have 8 SUMPRODUCT formulas on this work sheet and it takes 25 seconds to calculate. I know that there is probably a better way.

Thanks,
Steve

 

[byundt]

Steve,
Try to avoid using OFFSET, NOW, TODAY, INDIRECT, CELL, RAND and INFO because they are volatile functions that will recalculate whenever anything in the workbook changes. For your dynamic named ranges, you could use the non-volatile function INDEX instead:

Range: Shop
Formula:
=$A$2:INDEX('Data'!$A$2:$A$65536,COUNTA('Data'!$A$2:$A$65536))

Range: FromDate
Formula: =$B$2:INDEX('Data'!$B$2:$B$65536,COUNTA('Data'!$A$2:$A$65536))


Also, consider moving the MONTH and YEAR functions outside of the SUMPRODUCT. To do this, you would add some auxiliary columns that contain the formulas:
=MONTH(B2)
=YEAR(B2)
You would then refer to these columns in the SUMPRODUCT formula. The benefit of this approach is that the month and year don't have to be calculated on the fly.

Brad