Counting empty cells to the left

**DonkeyOte** · 09-22-2009, 06:48 AM

Could you post a version with your desired output (calculated manually of course) ... based on your last file I'm not sure where you want these results to appear nor from which date the calculation is meant to be based... presumably today ?

**Befuddled** · 09-22-2009, 06:54 AM

The area highlighted in yellow is what I would like to be able to calculate, I did the ones for Team Member "A" manually

**Befuddled** · 09-22-2009, 06:55 AM

calculation would be from the end of each month

**DonkeyOte** · 09-22-2009, 07:19 AM

Given the nature of your set-up and so as to reduce repetitive calcs I would be inclined to do something like:

BL56:BM56 (merged as per rows above)
=SUM(BJ56:BK56,DAY((("01-"&(MONTH(1&BL$29)+1))-1)))
copied across to CI56

Then to generate results

BZ30
=BZ$56-IF(SUM($AG30:AV30),MATCH(REPT("Z",255),$B3:INDEX($B3:$NB3,BZ$56)),0)
applied across matrix

Re: use of REPT("Z",255) see Bob Phillips' article on finding "last values": http://www.xldynamic.com/source/xld.LastValue.html

**Befuddled** · 09-22-2009, 10:15 PM

That formula gives me a #N/A error

**DonkeyOte** · 09-23-2009, 01:56 AM

Attachment represents that uploaded in post # 6 with addition of formulae as suggested in post # 8

**Befuddled** · 09-23-2009, 03:20 AM

When I tried to do the formulas in post 8. the formulas on row 56 were coming up with results of 1,2,3,4,5,6,7,8,9,10,11,12. Then when I copied the formula from the 1st cell (in row 56) on your last post, it came up with different #'s than in your spreadsheet. Sorry if I'm not following along too fast, I've only been messing with Excel for a short time and no one at my work has any knowledge for me to gleen from.