Sum a Range Dependent on Non-zero Entries
 

I'm developing a flexitime spreadsheet and I'm calculating whether I'm over
or under my contractual hours for the week. If there's a time worked for
Monday, I want to use the contractual hours for Monday only to calculate the
cumulative balance. If there's a time worked for Tuesday, I want to sum the
contractual hours for Monday & Tuesday. If there's a time worked for
Wednesday, I want to sum the contractual hours for Monday thro Wednesday, etc.

I have this rather unwieldy nested IF statement and wonder if there's a
neater way?

=IF(J38="","",IF(J39="",U38,IF(J40="",SUM(U38:U39) ,IF(J41="",SUM(U38:U40),IF(J42="",SUM(U38:U41),IF( J43="",SUM(U38:U42),))))))

The hours worked are in the J column and the contractual hours in the U
column. Rows 38 to 42 represent Monday thro Friday.
--
Richard

Search the web and raise money for charity at www.everyclick.com

Sum a Range Dependent on Non-zero Entries
 

See if this works for you:
=SUMIF(J38:J42,""&"""",U38:U42)
which is pretty much same as
=SUMPRODUCT(--(J38:J42<""),(U38:U42))

The first one should be a little faster.

They assume that after the first non-empty entry in column J that all other
cells in J will have some entry. That is, if J38 is empty, but J39 is not
empty, then J39:J42 would all have some entry in them.


"RichUE" wrote:

I'm developing a flexitime spreadsheet and I'm calculating whether I'm over
or under my contractual hours for the week. If there's a time worked for
Monday, I want to use the contractual hours for Monday only to calculate the
cumulative balance. If there's a time worked for Tuesday, I want to sum the
contractual hours for Monday & Tuesday. If there's a time worked for
Wednesday, I want to sum the contractual hours for Monday thro Wednesday, etc.

I have this rather unwieldy nested IF statement and wonder if there's a
neater way?

=IF(J38="","",IF(J39="",U38,IF(J40="",SUM(U38:U39) ,IF(J41="",SUM(U38:U40),IF(J42="",SUM(U38:U41),IF( J43="",SUM(U38:U42),))))))

The hours worked are in the J column and the contractual hours in the U
column. Rows 38 to 42 represent Monday thro Friday.
--
Richard

Search the web and raise money for charity at www.everyclick.com

Sum a Range Dependent on Non-zero Entries
 

Could you explain what the ""&"""" part means? It seems to be saying
"greater than AND empty".
--
Richard
Using Office 2003 & Win2kpro

Search the web and raise money for charity at www.everyclick.com


"JLatham" wrote:

See if this works for you:
=SUMIF(J38:J42,""&"""",U38:U42)
which is pretty much same as
=SUMPRODUCT(--(J38:J42<""),(U38:U42))

The first one should be a little faster.

They assume that after the first non-empty entry in column J that all other
cells in J will have some entry. That is, if J38 is empty, but J39 is not
empty, then J39:J42 would all have some entry in them.


"RichUE" wrote:

I'm developing a flexitime spreadsheet and I'm calculating whether I'm over
or under my contractual hours for the week. If there's a time worked for
Monday, I want to use the contractual hours for Monday only to calculate the
cumulative balance. If there's a time worked for Tuesday, I want to sum the
contractual hours for Monday & Tuesday. If there's a time worked for
Wednesday, I want to sum the contractual hours for Monday thro Wednesday, etc.

I have this rather unwieldy nested IF statement and wonder if there's a
neater way?

=IF(J38="","",IF(J39="",U38,IF(J40="",SUM(U38:U39) ,IF(J41="",SUM(U38:U40),IF(J42="",SUM(U38:U41),IF( J43="",SUM(U38:U42),))))))

The hours worked are in the J column and the contractual hours in the U
column. Rows 38 to 42 represent Monday thro Friday.
--
Richard

Search the web and raise money for charity at www.everyclick.com