conditional SUM()

Posted by Ian on July 23, 2001 5:39 AM

Hello all

In cells B6:F26 I have space for amount of hours in training each person does. B6:F6 is person 1. B6 is Monday's column.
In B27:F27 I have the amount of time for the breaks over the day (these apply to all the people in training).
In G6:G26 I need to add up the total breaks for the whole week.

Should be =sum(B27:F27)

BUT! what if 1 person was off say Tuesday, how do I conditionally let the breaks column NOT add in the breaks for days where the Hours Training are blank.

I tried:

{=SUM(B6:F6)*SUM(B27:F27>0)}

but hey I don't know nothing, Help! please.
I'm sure I could've done this yesterday and have forgotten everything.

Ian

Posted by Aladin Akyurek on July 23, 2001 7:01 AM

Ian,

Care to provide the data (if confidential, anonymize)? Activate an empty cell, type =, select the data range, and give it the combination the control+shift+enter. Go to the formula bar, select the formula, hit F9, copy the array that you see, and post it in the follow-up.

Aladin

=======

Posted by Ian on July 23, 2001 8:37 AM

Confused??

The values for my e.g. are {=(B6:F26)} Equal ={6,6,7,0,7}

to clarify A6 would be the persons name, say "Ian"
B6:F6 is the hours Ian was in training Mon through Friday

The values {=(B27:F27)} Equal ={0.5,0.5,0.5,0.5,0.5}

This row is the amount of time scheduled for breaks for the whole group (ROWS 6 - 26).
I've put 0.5 for each day but they could all be different.

You'll notice that Ian was NOT in for Thursdays training (the lazy bugger) so I need the formula to exclude Thursdays break, thus telling me how much time was spent on a break for Ian for the whole week.

Any clearer

Thanks

Ian

Posted by Aladin Akyurek on July 23, 2001 9:23 AM

Ian,

> Confused??

I admit I was.

> The values for my e.g. are {=(B6:F26)} Equal ={6,6,7,0,7}

> to clarify A6 would be the persons name, say "Ian"
> B6:F6 is the hours Ian was in training Mon through Friday

> The values {=(B27:F27)} Equal ={0.5,0.5,0.5,0.5,0.5}

> This row is the amount of time scheduled for breaks for the whole group (ROWS 6 - 26).
> I've put 0.5 for each day but they could all be different.

> You'll notice that Ian was NOT in for Thursdays training (the lazy bugger) so I need the formula to exclude Thursdays break, thus telling me how much time was spent on a break for Ian for the whole week.

> Any clearer

Yes. It's a "uniconditional" sum, it seems to me:

=SUMIF(B6:F6,">0",$B$27:$F$27)

Am I missing something?

Aladin

Posted by Ian on July 23, 2001 2:23 PM

How stupid do I feel, it looks right, I'll check tomorrow, and to think I just answered the above SUMIF() question. or did I get that wrong (these long days!!)