Excel 2008 : Problem w/"False" value in COUNTA & copying the formula

**so_fistica_ted** · 06-10-2010, 08:12 PM

Hello all.
See attached file please. (Sorry, just realized it works only with .xlsx format -XL 2008, tried to save it as .xls and the file came out all corrupted)
A1:MZ4 is an attendance record. Whatever I write on a cell on A4:MZ4 the cell changes color and I can see attendance at a glance.
NE2:ND57 is used for payments due. Choice of 2 weekly payment amounts: $230 (NB6) or $180 (NB5).
Thanks to help from NBVC, zbor and daddylonglegs I used a formula to check attendance during a week and defining what is the payment due for that week.
I defined 2 ranges for convenience, to “jump” from January (“Jan”, beginning of working area) to the payment area (“Summary”, end of working area)

Problem 1:
The formula at NE4 “=IF(COUNTIF(D4:H4,"End"),"End",IF(COUNTIF(D4:H4,"Deposit"),"",IF(COUNTA(D4:H4),IF($NE$2=2,$NB$5,IF($NE$2=3,$NB$6,"")))))”

Works just fine as long as there is attendance on at least 1 day. If there is not attendance for a week it “gives” the “False” value. I would like to have an empty cell instead. I have tried for hours, reading on the Internet and so, I guess it’s a matter of syntax, but due to my level of knowledge (or level of “un-knowledge”)…

I didn’t succeed to solve it.

Problem 2:
After the formula at NE4 will work I need to copy it to NE5:NE56 AND some additional columns.
Of course if I just copy and paste/paste special the “increments” will increase in 1’s and I need to “increase” the next formula by 7 (1 week) (the formula at NE5 contains the range D4:H4, if I copy & paste I will get at NE6 a D5:H5 range, instead of K4:O4 and so on).
I am sure there is an easier way than manually change each and every formula (I am not young enough to start and finish a task like that)…

I hope I explained my self, if not, please ask me.
Thanks in advance for any help.
Saul

**NBVC** · 06-10-2010, 10:52 PM

Try perhaps...

Please Login or Register  to view this content.

copied down

**so_fistica_ted** · 06-10-2010, 11:21 PM

Hi NBVC.
Copied and pasted your formula but it doesn't work.
I don't know if you tried to download the file I attached, but if you did it was corrupted (saved as .xls). It works only as .xlsx.
If you are able to open .xlsx, please try to download it and see how it works.
Thanks.
Saul

**NBVC** · 06-10-2010, 11:33 PM

Please Login or Register  to view this content.

sorry, I copied from wrong row...

Try the above instead.

If I enter End, in any cell between D4 and H4, I get End, etc...

**so_fistica_ted** · 06-11-2010, 02:08 PM

NBVC.
Thanks SOOOO much for your help!!!
It works JUST PERFECTLY!!!
I really appreciate you devoted from your time and expertise to help me.

I also need to add more amounts (not only $180 and $230), but I played a bit and found the way to add more IF's.
Also need to add several additional rows and also, assumed that if I change all the $4 with $5 it will work and... of course it does!

Now I am going to start reading and learning how and why it works and I hope to get one day to do something like this (and more) myself.

Have a wonderful weekend!
Saul