Summing a CF range

[Potoroo]

Hello,

I have attached a sample workbook.

I have used the following sumproduct formulas in CF and now need to sum the values in the range B11:B20 only where the CF is NOT red:

Please Login or Register  to view this content.

Is sumproduct the best way to get this total? If so, how do I include the range B11:B20 into the formula?

Is there a better way to do this? Please note: I am unable to use the ATP due to organisational policy. I also prefer not to use VBA in this instance.

Greatly appreciate any assistance as I am still learning.

Many thanks.

[Fotis1991]

Ηι

One way, using a helper column, is this.

In L11(L, is a helper column in my example), put this and copy down.

Please Login or Register  to view this content.

Then in B22, use this.

Please Login or Register  to view this content.

Is this, works for you?

[benishiryo]

hi Potoroo, not sure if i understood it correctly. 23rd Apr'12 is a Monday. 25th Apr'12 is a holiday, so it should be excluded. 30th Apr'12 is the following Monday. So including the 1st Mon, there should be 5 working days & should be orange in colour instead? here's how i got it:
=NETWORKDAYS($A11,$B$3,Holidays)

if you need to exclude the 1st day:
=NETWORKDAYS($A11,$B$3,Holidays)-1

you can use the above for your CF if you want to. for your summation, there's probably a better way but i cant think of 1 without a helper column. my helper column will be in C11:C20
=NETWORKDAYS($A11:$A11,$B$3,Holidays)<6

you can colour this font in white to hide it. then my summation will be:
=SUMPRODUCT(($C$11:$C$20=TRUE)*($B$11:$B$20))

[Potoroo]

Thank you Fotis1991.

Your solution works beautifully.

Much appreciated!

---------- Post added at 06:27 PM ---------- Previous post was at 06:23 PM ----------

Thank you benishiryo for taking the time to respond.

Unfortunately, I cannot utilise the NETWORKDAYS function due to organisational rules (not authorised to install the ATP).

Also, to clarify the numbers of days, the first day of the range is not counted in my scenario so I have deducted a day in my formulas.

I really appreciate your kind assistance and will be able to revisit this solution when the organisation updates to Office 2010 in the near future!

[Fotis1991]

You are welcome.

Thanks for the feed back and also for the reb*