I have a range of cells, F4:T4, that have a range of values from 1 to 100 or an X or an M in each. The M can be ignored since it is valued at 0 and Excel Sum seems to ignore the cells with letters. However, if it's not in a SUM function it will need to be tested for and set to 0 in the total. Each cell which has an X must be tested for and set at a value of 10 when doing the Sum for the range of cells. Any suggestions?
I should note that the values in each cell, X or M, must remain unchanged.
Found an answer. It's certainly not elegant, but it does work. Just glad the number of columns in the row isn't greater. Here's the code.
=SUM(IF(F4="X";10;F4);IF(G4="X";10;G4);IF(H4="X";10;H4);IF(I4="X";10;I4);IF(J4="X";10;J4);IF(K4="X";10;K4);IF(L4="X";10;L4);IF(M4="X";10;M4);IF(N4="X";10;N4);IF(O4="X";10;O4);IF(P4="X";10;P4);IF(Q4="X";10;Q4);IF(R4="X";10;R4);IF(S4="X";10;S4);IF(T4="X";10;T4))
test this array formula (CTRL+SHFT+ENTER):
Please Login or Register to view this content.
- i.s.z -
CSE, aka Array aka { }, formulae are confirmed with CONTROL+SHIFT+ENTER.
Replace commas ( , ) with semicolons ( ; ) in formulae, if your locale setting demands.
All good ideas are courtesy resources from this forum as well as others around the web.
- e.o.m -
Wow! Thanks icestaionzebra! I thought there should be a better way but none of the things I tried worked. Obviously I don't know enough about using the functions.
Thanks again.
Gregg
Spoke too soon. It gives a 5078 error when it's on my box. Ah well.
wow, never heard of that one before. can you provide more detail?
Sorry, should have proof read before hitting send. It's a 508 error. I can't find anything that's paired and not matching.
dang, i did not observe until now that you are using MS Excel 97. i do not have any version other than 2007.
That's ok, thanks for the help though. I'll keep this code so I have it when I finally get around to upgrading.
508 error? where does it say that in excel?
isnt that an open office error code? if so you need to change, to ;
=SUM(IF(F4:T4="m";0;IF(F4:T4="x";10;F4:T4)))
or maybe if you are using excel the same could apply if your regional settings are set to ;for seperator
and fyi that doesn't need array entering works fine as it is
but in calc it wont workatall unless you re enter it but you cant just type f4:e4
you have to select it with the mouse in each position its used in the formula and then it still needs array entering
maybe just
=SUM(F4:T4)+COUNTIF(F4:T4;"x")*10 would suffice
Last edited by martindwilson; 07-15-2012 at 02:06 PM.
"Unless otherwise stated all my comments are directed at OP"
Mojito connoisseur and now happily retired
where does code go ?
look here
how to insert code
how to enter array formula
why use -- in sumproduct
recommended reading
wiki Mojito
how to say no convincingly
most important thing you need
Martin Wilson: SPV
and RSMBC
Hi Martin,
The second set of code did accomplish what I am trying to do. Thanks for the fresh approach. Works perfectly.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks