|
| If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|||||||
|
|
Thread Tools | Display Modes |
|
#1
June 23rd, 2009, 11:50 AM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
"Count non blanks" if cells don't equal certain values
I need to figure out a formula that counts the non blanks in a row, but omits
non blanks equalling "UA", "UE" and "AA". Can anyone give me a simple formula please, I've tried all sorts of IFs, AND's and MAYBE's thanks! 
|
|
#2
June 23rd, 2009, 12:33 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
"Count non blanks" if cells don't equal certain values
If you want to exclude only the three values you mentioned then try
=COUNTA(B1:B9)-COUNTIF(B1:B9,"EE")-COUNTIF(B1:B9,"UA")-COUNTIF(B1:B9,"UE") You can comeup with more elegant formulas but all of them will essentially do the same process... Adjust the range according to your data. Click 'Yes' if this helped. "thegymshoe" wrote: I need to figure out a formula that counts the non blanks in a row, but omits non blanks equalling "UA", "UE" and "AA". Can anyone give me a simple formula please, I've tried all sorts of IFs, AND's and MAYBE's thanks!
|
|
#3
June 23rd, 2009, 12:43 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
"Count non blanks" if cells don't equal certain values
=256-COUNTBLANK(1:1)-COUNTIF(1:1,"AA")-COUNTIF(1:1, "UE")-COUNTIF(1:1,"UA")
-- Gary''s Student - gsnu200858
|
|
#4
June 23rd, 2009, 02:04 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
"Count non blanks" if cells don't equal certain values
If I have 8 values I need to omit, this might be quite longwinded - is there
any way of condensing it so that you only need to put the range in twice? Thanks "Sheeloo" wrote: If you want to exclude only the three values you mentioned then try =COUNTA(B1:B9)-COUNTIF(B1:B9,"EE")-COUNTIF(B1:B9,"UA")-COUNTIF(B1:B9,"UE") You can comeup with more elegant formulas but all of them will essentially do the same process... Adjust the range according to your data. Click 'Yes' if this helped. "thegymshoe" wrote: I need to figure out a formula that counts the non blanks in a row, but omits non blanks equalling "UA", "UE" and "AA". Can anyone give me a simple formula please, I've tried all sorts of IFs, AND's and MAYBE's thanks!
|
|
#5
June 23rd, 2009, 04:01 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
"Count non blanks" if cells don't equal certain values
Replace the characters within the quotes with your values and adjust for
your ranges: =COUNTA(A1:Z1)-SUM(COUNTIF(A1:Z1,{"AA","BB","CC","DD","EE","FF"," GG","HH"})) -- HTH, RD ================================================== === Please keep all correspondence within the Group, so all may benefit! ================================================== === "thegymshoe" wrote in message ... If I have 8 values I need to omit, this might be quite longwinded - is there any way of condensing it so that you only need to put the range in twice? Thanks "Sheeloo" wrote: If you want to exclude only the three values you mentioned then try =COUNTA(B1:B9)-COUNTIF(B1:B9,"EE")-COUNTIF(B1:B9,"UA")-COUNTIF(B1:B9,"UE") You can comeup with more elegant formulas but all of them will essentially do the same process... Adjust the range according to your data. Click 'Yes' if this helped. "thegymshoe" wrote: I need to figure out a formula that counts the non blanks in a row, but omits non blanks equalling "UA", "UE" and "AA". Can anyone give me a simple formula please, I've tried all sorts of IFs, AND's and MAYBE's thanks!
|
|
#6
June 23rd, 2009, 04:11 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
"Count non blanks" if cells don't equal certain values
Sorry must have posted it wrong - how do you include everyone in the reply? As you can see Im new to this! The answer is what I'm looking for - excellent thanks "RagDyeR" wrote: Replace the characters within the quotes with your values and adjust for your ranges: =COUNTA(A1:Z1)-SUM(COUNTIF(A1:Z1,{"AA","BB","CC","DD","EE","FF"," GG","HH"})) -- HTH, RD ================================================== === Please keep all correspondence within the Group, so all may benefit! ================================================== === "thegymshoe" wrote in message ... If I have 8 values I need to omit, this might be quite longwinded - is there any way of condensing it so that you only need to put the range in twice? Thanks "Sheeloo" wrote: If you want to exclude only the three values you mentioned then try =COUNTA(B1:B9)-COUNTIF(B1:B9,"EE")-COUNTIF(B1:B9,"UA")-COUNTIF(B1:B9,"UE") You can comeup with more elegant formulas but all of them will essentially do the same process... Adjust the range according to your data. Click 'Yes' if this helped. "thegymshoe" wrote: I need to figure out a formula that counts the non blanks in a row, but omits non blanks equalling "UA", "UE" and "AA". Can anyone give me a simple formula please, I've tried all sorts of IFs, AND's and MAYBE's thanks!
|
|
#7
June 23rd, 2009, 08:29 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
"Count non blanks" if cells don't equal certain values
Don't understand your comment ... I don't see anything wrong with your
posting! Anyway, you're welcome and thank you for the feed-back. -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "thegymshoe" wrote in message news  Sorry must have posted it wrong - how do you include everyone in the reply? As you can see Im new to this! The answer is what I'm looking for - excellent thanks "RagDyeR" wrote: Replace the characters within the quotes with your values and adjust for your ranges: =COUNTA(A1:Z1)-SUM(COUNTIF(A1:Z1,{"AA","BB","CC","DD","EE","FF"," GG","HH"})) -- HTH, RD ================================================== === Please keep all correspondence within the Group, so all may benefit! ================================================== === "thegymshoe" wrote in message ... If I have 8 values I need to omit, this might be quite longwinded - is there any way of condensing it so that you only need to put the range in twice? Thanks "Sheeloo" wrote: If you want to exclude only the three values you mentioned then try =COUNTA(B1:B9)-COUNTIF(B1:B9,"EE")-COUNTIF(B1:B9,"UA")-COUNTIF(B1:B9,"UE") You can comeup with more elegant formulas but all of them will essentially do the same process... Adjust the range according to your data. Click 'Yes' if this helped. "thegymshoe" wrote: I need to figure out a formula that counts the non blanks in a row, but omits non blanks equalling "UA", "UE" and "AA". Can anyone give me a simple formula please, I've tried all sorts of IFs, AND's and MAYBE's thanks!
|
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
