Counting prob

**superkopite** · 01-21-2006, 07:52 AM

hiya guys and gals

I have a big problem. I need to make a spreadsheet for work. i'll try to explain as best as i can.

Cells A6 to A64 contain the numbers 1 to 52, these represent the weeks of the year. In cells B6 to B64 any hours that a staff member has been off will be recorded. Cell C1 contains a member of staffs contracted hours. Cell D1 contains the current week number. Cell E1 need to count how many hours (from B6 to B64) were off, but only the last 26 weeks. For example if the current week is 30 it would need to calculate from wk 4 to wk 30 but if the current wk is week 5 it would need to add week 1 to wk 5 and wk 31 to wk 52.

I have tried loads of ways of doing this and just end up confusing myself to the point of suicide!

I need HELP!!!!!!!!!!!!!!

Please to the sake of my blood pressure, help me find a solution!

Regards

James

**Mikeopolo** · 01-21-2006, 09:21 AM

Hi, I suggest the following method...

btw, the numbers 1 to 52 will go in to cells A6 to A57. And if the current week is 5, then you want the hours from weeks 32 (not 31) to 52, and 1 to 5.

I suggest using range names, to make the formula easier to understand.

So name the week numbers "weeks"
Name the cell with the current week "current"
(To name a cell or range, select it then type the name in to the box on the left of the formula bar which has the cell ref in it.

Then in cell E1 type:
=SUMPRODUCT(((weeks>(current-26))*(weeks<=current))*(weeks+26>current)*(B6:B57))

(copy and pasting this text would be easier)

This is an undocumented use for Sumproduct, and is very useful for multiple criteria. It works roughly as follows:

If:
(The week number is greater than (current week less 26)
and
The week number is less than or equal to the current week )
and
The week number + 26 is greater than the current week )

then add the corresponding value in column B, rows 6 to 57.

Hope it works for you...

Regards
Mike

**superkopite** · 01-21-2006, 09:38 AM

Thanks that is alot simpler to understand than what i was trying. However this formula doesn't work if the week number is less than 26. But i think the answer is close.

Cheers

James

**Mikeopolo** · 01-21-2006, 09:49 AM

Hi James

Sorry about that, I didn't notice in later testing.

The sumproduct conditions are additive, ie only AND.

You need something slightly different.

Try pasting this formula into C6, then copy/paste in C7 thru C57:

=IF(OR(AND(A6>(current-26),A6<=current),A6>(current+26)),B6,0)

Then in E1 sum this range of cells.

Not as elegant as the single cell formula, but correct; of course it assumes that this range is available.

I was using this method to cross-check my sumproduct formula.

Regards
Mike

**superkopite** · 01-21-2006, 09:53 AM

I bow down to your greatness.

**Mikeopolo** · 01-21-2006, 09:57 AM

Glad to be of help!

Mike

**daddylonglegs** · 01-21-2006, 01:34 PM

You could use this formula in E1 to calculate with one formula

=SUMPRODUCT(--(A6:A57+(A6:A57<=D1)*52>=D1+26),B6:B57)

Bob Phillips · 01-21-2006, 01:50 PM

Mike,

You can create an OR condition in SUMPRODUCT with +

=SUMPRODUCT(((weeks<=current)*(weeks>current-26))+((weeks>current+26)*(weeks
>current)),B6:B57)

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"Mikeopolo" <[email protected]> wrote
in message news:[email protected]...
>
> Hi James
>
> Sorry about that, I didn't notice in later testing.
>
> The sumproduct conditions are additive, ie only AND.
>
> You need something slightly different.
>
> Try pasting this formula into C6, then copy/paste in C7 thru C57:
>
> =IF(OR(AND(A6>(current-26),A6<=current),A6>(current+26)),B6,0)
>
> Then in E1 sum this range of cells.
>
> Not as elegant as the single cell formula, but correct; of course it
> assumes that this range is available.
>
> I was using this method to cross-check my sumproduct formula.
>
> Regards
> Mike
>
>
> --
> Mikeopolo
> ------------------------------------------------------------------------
> Mikeopolo's Profile:
http://www.excelforum.com/member.php...o&userid=18570
> View this thread: http://www.excelforum.com/showthread...hreadid=503604
>

**superkopite** · 01-21-2006, 04:04 PM

Thanks Daddy and Bob

I tried those solutions too and all three work!

Gosh i went from not being able to work out a formula to being spoilt for choice!!!

in the end i went with Mikeopolo my simple mind could just about conprehend this one!!!!

thanks alot

James

**Mikeopolo** · 01-21-2006, 05:51 PM

Thank you both for your postings, I learn more each day. I'd appreciate your explanations for the conditions you have used.

Could you also explain the -- ? I know only that it forces the treatment of numbers as numbers, and that without it (in this case) the formula returns zero.

Regards
Mike

**daddylonglegs** · 01-21-2006, 06:02 PM

In the formula I posted

=SUMPRODUCT(--(A6:A57+(A6:A57<=D1)*52>=D1+26),B6:B57)

the part

(A6:A57+(A6:A57<=D1)*52>=D1+26) returns an array of TRUE/FALSE values something like {TRUE;FALSE;TRUE....etc..}

the -- coerces this array to 1/0 values, e.g. {1,0,1....etc}

SUMPRODUCT then multiplies the corresponding value in each array and adds the results so you get

(1*B6)+(0*B7)+(1*B8)....etc.

the effect is to add the value of B in every row where the criteria is fulfilled.

Of course you don't need to use --, you could also use +0 or *1, in short a mathematical operation that doesn't change the value.

Bob Phillips · 01-21-2006, 08:30 PM

See http://www.xldynamic.com/source/xld.SUMPRODUCT.html for a detailed
explanation.

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"Mikeopolo" <[email protected]> wrote
in message news:[email protected]...
>
> Thank you both for your postings, I learn more each day. I'd appreciate
> your explanations for the conditions you have used.
>
> Could you also explain the -- ? I know only that it forces the
> treatment of numbers as numbers, and that without it (in this case) the
> formula returns zero.
>
> Regards
> Mike
>
>
> --
> Mikeopolo
> ------------------------------------------------------------------------
> Mikeopolo's Profile:
http://www.excelforum.com/member.php...o&userid=18570
> View this thread: http://www.excelforum.com/showthread...hreadid=503604
>

**Mikeopolo** · 01-22-2006, 10:31 PM

Hi James

Just for my own interest, i corrected my original sumproduct formula following advice from the other posters:

=SUMPRODUCT(((weeks>current-26)*(weeks<=current)+(weeks>current+26))*(B6:B57))

I've put a bracket around the whole set of conditions, replaced one * with + (meaning OR), and corrected an error in the 3rd condition. This should now work.

I thought the first two conditions should be bracketed (they are AND'd), but the * symbol looks like it has a higher priority than the + symbol, so brackets are not required.

Regards
Mike

Bob Phillips · 01-23-2006, 05:25 AM

Mike

BODMAS

Brackets, Order, Division, Multiplication, Addition, Subtraction.

Learnt that as a babe <G>

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"Mikeopolo" <[email protected]> wrote
in message news:[email protected]...
>
> Hi James
>
> Just for my own interest, i corrected my original sumproduct formula
> following advice from the other posters:
>
>
=SUMPRODUCT(((weeks>current-26)*(weeks<=current)+(weeks>current+26))*(B6:B57
))
>
> I've put a bracket around the whole set of conditions, replaced one *
> with + (meaning OR), and corrected an error in the 3rd condition. This
> should now work.
>
> I thought the first two conditions should be bracketed (they are
> AND'd), but the * symbol looks like it has a higher priority than the +
> symbol, so brackets are not required.
>
> Regards
> Mike
>
>
> --
> Mikeopolo
> ------------------------------------------------------------------------
> Mikeopolo's Profile:
http://www.excelforum.com/member.php...o&userid=18570
> View this thread: http://www.excelforum.com/showthread...hreadid=503604
>