Formula help needed

B

Ben Mehling

I need to calculate a year-to-date total from a list of daily production
values. Each day has individual production totals, and it's own daily total,
and I need to have the daily totals added up into an annual total elsewhere
on the worksheet. I have asked for help on this before, but then no longer
needed the spreadsheet and foolishly deleted it without saving the formula I
was using to do this with. A mistake I won't be making again, believe me!

Any suggestion as to a direction to go here will be helpful! Thank you!
 
R

Roger Govier

Hi Ben

With dates in A and Values in B with row 1 containing headers, then
=SUMPRODUCT(($A2:$A!000<=TODAY())*($B$:$B1000))

Change ranges to suit but make them of equal size.
 
R

RichardSchollar

Ben

I'm not finding it easy to visualize exactly how you have your dat
organised, but if it's a case of eg in column B you have you
individual production totals *and* your daily production totals (ie
summation of the individual production totals) then you have basicall
doubled up on the values, so one way to get the YTD would be:

=SUM(B:B)/2

Obviusly you can adjust the range to suit.

Is this what you meant, or have I missed the point?

Best regards

Richar
 
B

Bob Phillips

Small typo

=SUMPRODUCT(($A2:$A1000<=TODAY())*($B$:$B1000))

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)
 
B

Ben Mehling

I tried it, but got an error. However, a little tinkering got the result I
was looking for. Here's the formula I ended up with:

=SUMIF($B7:$B1000, "Total", $C7:$C1000)

This is actually much simpler than the one I had before.

Thank you for your assistance Roger!
 
R

Roger Govier

Hi Ben

As Bob pointed out my formula had a typo, it should have been
$B$2:$B$1000.
However, that would not have worked, as you have now described the data
somewhat differently to that which I had assumed, an column B contains
Text.

Delighted that you found a solution, and thanks for posting back to let
us know.
 
Top