Sum up status per row

[TomV]

HI, I'm new at Excel and just trying to get by with some online help and the Excel Bible book, but I still don't quite know if its possible to do what I want to do. I'm not quite familiar with how to write 'criterias' inside a formula.

Please take a look at my sample Excel 2003 file to see if it make sense?

Any response would be appreciated.

Thanks !

Tom

[TomV]

OK so really 'Attach Files in Help Forum ?' shouldn't be the Title to my post but 'How do I write a proper Formula for this chart?'

[JBeaucaire]

Return to your first post and click on the EDIT button, then the GO ADVANCED button, change the title there.

[teylyn]

Hi TomV,

welcome to the forum. An appropriate title for your thread would be "Sum up status per row". I'll change the title for you this time, but for your next thread try to describe the isse in a few simple words.

Looking at your spreadsheet, I see several hurdles that will make a solution difficult to achieve in XL 2003 without the use of VBA.

- Your values in C4 to Q12 contain leading spaces, so a comparison with the values in C20 to C22, for example with COUNTIF() will always return FALSE. You need to remove the spaces or resort to a more complicated SUMPRODUCT construct.
- you distinguish between two statuses by applying a cell color to the same symbol. Excel 2003 cannot evaluate cell color with formulae. To find a solution without VBA, you would need to come up with a different symbol for what currently is the white cell with the check mark, for example Q instead of P with Wingdings2

With these corrections in place, you can use this formula in S4 and copy down

Please Login or Register  to view this content.

see attached with the above formula applied in column T for perusal.

[TomV]

Thanks for your reply, teylyn. Guess trying to count numerical cells is not easy for excel. I was hoping to be able to make excel count cells based on letters or symbols. Aha, so I can assign a value to letters 'P'=80

ie: IF all Cells in one Row are all =80 then output 1, if not output 2 or 3 (depending on second or third or more sub-conditions)

So Excel does count spaces, correct? (CODE returns a '32')
Is there a way to say:
If Cell contains 'P' (even if it has a number and space: like "Sep.25 P")
then do this or that...

Maybe I'll have to learn that other programming VBA then. I'll try to learn and understand your formula.

Ha ha, talk about doing something totally over my head here. Its easier to come up with concepts for Excel than it is to actually make it work.

Happy New Year !

Tom

And thanks for changing the title for me, I'll keep JBeaucaire's tip in mind for future posts.

[Humanist]

Hi TomV,
Excel 2003 cannot evaluate cell color with formulae.

Is this something that will change with Excel 2010? I would love to be able to evaluate cells by format.


Thanks,
Josh

[teylyn]

Humanist ...

Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread.