| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| SUM a range dependent on dates | Excel Discussion (Misc queries) | |||
| Data Validation via Dependent List defined by Dynamic Range | Excel Discussion (Misc queries) | |||
| How to count dates within a certain range in a column with mutiple date range entries | Excel Worksheet Functions | |||
| count no. of entries in range for a day | Excel Worksheet Functions | |||
| How do I count data in range A:A that is dependent upon criteria . | Excel Worksheet Functions | |||
Linear Mode
