| Home |
| Search |
| Today's Posts |
|
#1
|
|||
|
|||
countif??
Need to add a column that has either a "P" or an "X". I can figure out how
to add "p's" or "x's"( =COUNTIF(D2:D65,"p") but not both ("p and/or x"). It was suggested I use the countif. Suggestions and thanks, L. |
|
#2
|
|||
|
|||
|
WOW- thanks. Have a great new year. L. "Jason Morin" wrote in message ... One way: =SUM(COUNTIF(D2:D65,{"p","x"})) HTH Jason Atlanta, GA -----Original Message----- Need to add a column that has either a "P" or an "X". I can figure out how to add "p's" or "x's"( =COUNTIF(D2:D65,"p") but not both ("p and/or x"). It was suggested I use the countif. Suggestions and thanks, L. . |
|
#3
|
|||
|
|||
|
"L/P" wrote in message
... Need to add a column that has either a "P" or an "X". I can figure out how to add "p's" or "x's"( =COUNTIF(D2:D65,"p") but not both ("p and/or x"). It was suggested I use the countif. Err, =COUNTIF(D2:D65,"p")+COUNTIF(D2:D65,"x") , ? HTH, Andy |
|
#4
|
|||
|
|||
|
One way:
=SUM(COUNTIF(D2:D65,{"p","x"})) HTH Jason Atlanta, GA -----Original Message----- Need to add a column that has either a "P" or an "X". I can figure out how to add "p's" or "x's"( =COUNTIF(D2:D65,"p") but not both ("p and/or x"). It was suggested I use the countif. Suggestions and thanks, L. . |
|
#5
|
|||
|
|||
|
Here's another problem I can't figure out. I have made a monthly "deposit" sheet with: A B C D E F G H 1 Units $ Due Deposit # 1 Deposit # 2 Deposit # 3 2 Amount Paid Amount Paid Amount Paid 3 1203 $500 4 1204 $600 5 1205 $450 .. .. 63 1263 $500 64 1264 $450 When a correct deposit is made in Amount (column C,E or G) I want a "P" inserted in column D, F or H. If not correct amount "X". The formula I came up with would be =IF(C3=B3,"p","x"). Problem is that the entire sheet starts out with x's..... Trying to get a blank in columns D, F and H until something is entered in columns C,E or G. Help please, the entire sheet is filled with x's and each unit only makes one deposit a month. L. "L/P" wrote in message ... WOW- thanks. Have a great new year. L. "Jason Morin" wrote in message ... One way: =SUM(COUNTIF(D2:D65,{"p","x"})) HTH Jason Atlanta, GA -----Original Message----- Need to add a column that has either a "P" or an "X". I can figure out how to add "p's" or "x's"( =COUNTIF(D2:D65,"p") but not both ("p and/or x"). It was suggested I use the countif. Suggestions and thanks, L. . |
|
#6
|
|||
|
|||
|
Again, thanks. Is there a good simple to understand book out there I could get that would help me? The HELP menu apparently can't. :-) L. "Bob Phillips" wrote in message ... =IF(C3="","",IF(C3=B3,"p","x")) -- HTH RP (remove nothere from the email address if mailing direct) "L/P" wrote in message ... Here's another problem I can't figure out. I have made a monthly "deposit" sheet with: A B C D E F G H 1 Units $ Due Deposit # 1 Deposit # 2 Deposit # 3 2 Amount Paid Amount Paid Amount Paid 3 1203 $500 4 1204 $600 5 1205 $450 . . 63 1263 $500 64 1264 $450 When a correct deposit is made in Amount (column C,E or G) I want a "P" inserted in column D, F or H. If not correct amount "X". The formula I came up with would be =IF(C3=B3,"p","x"). Problem is that the entire sheet starts out with x's..... Trying to get a blank in columns D, F and H until something is entered in columns C,E or G. Help please, the entire sheet is filled with x's and each unit only makes one deposit a month. L. "L/P" wrote in message ... WOW- thanks. Have a great new year. L. "Jason Morin" wrote in message ... One way: =SUM(COUNTIF(D2:D65,{"p","x"})) HTH Jason Atlanta, GA -----Original Message----- Need to add a column that has either a "P" or an "X". I can figure out how to add "p's" or "x's"( =COUNTIF(D2:D65,"p") but not both ("p and/or x"). It was suggested I use the countif. Suggestions and thanks, L. . |
|
#7
|
|||
|
|||
|
=IF(C3="","",IF(C3=B3,"p","x"))
-- HTH RP (remove nothere from the email address if mailing direct) "L/P" wrote in message ... Here's another problem I can't figure out. I have made a monthly "deposit" sheet with: A B C D E F G H 1 Units $ Due Deposit # 1 Deposit # 2 Deposit # 3 2 Amount Paid Amount Paid Amount Paid 3 1203 $500 4 1204 $600 5 1205 $450 . . 63 1263 $500 64 1264 $450 When a correct deposit is made in Amount (column C,E or G) I want a "P" inserted in column D, F or H. If not correct amount "X". The formula I came up with would be =IF(C3=B3,"p","x"). Problem is that the entire sheet starts out with x's..... Trying to get a blank in columns D, F and H until something is entered in columns C,E or G. Help please, the entire sheet is filled with x's and each unit only makes one deposit a month. L. "L/P" wrote in message ... WOW- thanks. Have a great new year. L. "Jason Morin" wrote in message ... One way: =SUM(COUNTIF(D2:D65,{"p","x"})) HTH Jason Atlanta, GA -----Original Message----- Need to add a column that has either a "P" or an "X". I can figure out how to add "p's" or "x's"( =COUNTIF(D2:D65,"p") but not both ("p and/or x"). It was suggested I use the countif. Suggestions and thanks, L. . |
|
#8
|
|||
|
|||
|
John Walkenbach's Excel Formulas book is probably best for what you want?
-- HTH RP (remove nothere from the email address if mailing direct) "L/P" wrote in message ... Again, thanks. Is there a good simple to understand book out there I could get that would help me? The HELP menu apparently can't. :-) L. "Bob Phillips" wrote in message ... =IF(C3="","",IF(C3=B3,"p","x")) -- HTH RP (remove nothere from the email address if mailing direct) "L/P" wrote in message ... Here's another problem I can't figure out. I have made a monthly "deposit" sheet with: A B C D E F G H 1 Units $ Due Deposit # 1 Deposit # 2 Deposit # 3 2 Amount Paid Amount Paid Amount Paid 3 1203 $500 4 1204 $600 5 1205 $450 . . 63 1263 $500 64 1264 $450 When a correct deposit is made in Amount (column C,E or G) I want a "P" inserted in column D, F or H. If not correct amount "X". The formula I came up with would be =IF(C3=B3,"p","x"). Problem is that the entire sheet starts out with x's..... Trying to get a blank in columns D, F and H until something is entered in columns C,E or G. Help please, the entire sheet is filled with x's and each unit only makes one deposit a month. L. "L/P" wrote in message ... WOW- thanks. Have a great new year. L. "Jason Morin" wrote in message ... One way: =SUM(COUNTIF(D2:D65,{"p","x"})) HTH Jason Atlanta, GA -----Original Message----- Need to add a column that has either a "P" or an "X". I can figure out how to add "p's" or "x's"( =COUNTIF(D2:D65,"p") but not both ("p and/or x"). It was suggested I use the countif. Suggestions and thanks, L. . |
|
#9
|
|||
|
|||
|
=IF(SUM('January 2005'!C3,'January 2005'!E3,'January 2005'!G3)='January 2005'!B3,"P","X",IF(SUM('January 2005'!C3,'January 2005'!E3,'January 2005'!G3)='January 2005'!B3,"","")) How can I get around having "too many arguments" error msg? This is a yearly summary sheet(12 columns, one for each month) that would auto add the P or X from the deposit sheets. I started out with: =IF(SUM('January 2005'!C3,'January 2005'!E3,'January 2005'!G3)='January 2005'!B3,"P","X") pulling the P or X for the monthly deposit sheets. BUT, again, I ended up with a bunch of x's in blank cells. L. "Bob Phillips" wrote in message ... John Walkenbach's Excel Formulas book is probably best for what you want? -- HTH RP (remove nothere from the email address if mailing direct) "L/P" wrote in message ... Again, thanks. Is there a good simple to understand book out there I could get that would help me? The HELP menu apparently can't. :-) L. "Bob Phillips" wrote in message ... =IF(C3="","",IF(C3=B3,"p","x")) -- HTH RP (remove nothere from the email address if mailing direct) "L/P" wrote in message ... Here's another problem I can't figure out. I have made a monthly "deposit" sheet with: A B C D E F G H 1 Units $ Due Deposit # 1 Deposit # 2 Deposit # 3 2 Amount Paid Amount Paid Amount Paid 3 1203 $500 4 1204 $600 5 1205 $450 . . 63 1263 $500 64 1264 $450 When a correct deposit is made in Amount (column C,E or G) I want a "P" inserted in column D, F or H. If not correct amount "X". The formula I came up with would be =IF(C3=B3,"p","x"). Problem is that the entire sheet starts out with x's..... Trying to get a blank in columns D, F and H until something is entered in columns C,E or G. Help please, the entire sheet is filled with x's and each unit only makes one deposit a month. L. "L/P" wrote in message ... WOW- thanks. Have a great new year. L. "Jason Morin" wrote in message ... One way: =SUM(COUNTIF(D2:D65,{"p","x"})) HTH Jason Atlanta, GA -----Original Message----- Need to add a column that has either a "P" or an "X". I can figure out how to add "p's" or "x's"( =COUNTIF(D2:D65,"p") but not both ("p and/or x"). It was suggested I use the countif. Suggestions and thanks, L. . |
|
#10
|
|||
|
|||
|
Your first If statement has 4 arguments
=if(condition,what to do if true,what to do if false, What to do if ????) You can only have what to do for True or False -- Regards, Tom Ogilvy "L/P" wrote in message ... =IF(SUM('January 2005'!C3,'January 2005'!E3,'January 2005'!G3)='January 2005'!B3,"P","X",IF(SUM('January 2005'!C3,'January 2005'!E3,'January 2005'!G3)='January 2005'!B3,"","")) How can I get around having "too many arguments" error msg? This is a yearly summary sheet(12 columns, one for each month) that would auto add the P or X from the deposit sheets. I started out with: =IF(SUM('January 2005'!C3,'January 2005'!E3,'January 2005'!G3)='January 2005'!B3,"P","X") pulling the P or X for the monthly deposit sheets. BUT, again, I ended up with a bunch of x's in blank cells. L. "Bob Phillips" wrote in message ... John Walkenbach's Excel Formulas book is probably best for what you want? -- HTH RP (remove nothere from the email address if mailing direct) "L/P" wrote in message ... Again, thanks. Is there a good simple to understand book out there I could get that would help me? The HELP menu apparently can't. :-) L. "Bob Phillips" wrote in message ... =IF(C3="","",IF(C3=B3,"p","x")) -- HTH RP (remove nothere from the email address if mailing direct) "L/P" wrote in message ... Here's another problem I can't figure out. I have made a monthly "deposit" sheet with: A B C D E F G H 1 Units $ Due Deposit # 1 Deposit # 2 Deposit # 3 2 Amount Paid Amount Paid Amount Paid 3 1203 $500 4 1204 $600 5 1205 $450 . . 63 1263 $500 64 1264 $450 When a correct deposit is made in Amount (column C,E or G) I want a "P" inserted in column D, F or H. If not correct amount "X". The formula I came up with would be =IF(C3=B3,"p","x"). Problem is that the entire sheet starts out with x's..... Trying to get a blank in columns D, F and H until something is entered in columns C,E or G. Help please, the entire sheet is filled with x's and each unit only makes one deposit a month. L. "L/P" wrote in message ... WOW- thanks. Have a great new year. L. "Jason Morin" wrote in message ... One way: =SUM(COUNTIF(D2:D65,{"p","x"})) HTH Jason Atlanta, GA -----Original Message----- Need to add a column that has either a "P" or an "X". I can figure out how to add "p's" or "x's"( =COUNTIF(D2:D65,"p") but not both ("p and/or x"). It was suggested I use the countif. Suggestions and thanks, L. . |
|
#11
|
|||
|
|||
|
What I'm trying to do is keep cells blank, on the Summary sheet, unless
there is something in them. Then check the 3 cells and add the P or X. I tried to use the example from Bob, =IF(C3="","",IF(C3=B3,"p","x")) which does that on a single cell on the January 2005 sheet. I even tried to keep it simple and just test a single cell I know to be blank but then the cell stays blank even when there is a P or X in them!! L. "Tom Ogilvy" wrote in message ... Your first If statement has 4 arguments =if(condition,what to do if true,what to do if false, What to do if ????) You can only have what to do for True or False -- Regards, Tom Ogilvy "L/P" wrote in message ... =IF(SUM('January 2005'!C3,'January 2005'!E3,'January 2005'!G3)='January 2005'!B3,"P","X",IF(SUM('January 2005'!C3,'January 2005'!E3,'January 2005'!G3)='January 2005'!B3,"","")) How can I get around having "too many arguments" error msg? This is a yearly summary sheet(12 columns, one for each month) that would auto add the P or X from the deposit sheets. I started out with: =IF(SUM('January 2005'!C3,'January 2005'!E3,'January 2005'!G3)='January 2005'!B3,"P","X") pulling the P or X for the monthly deposit sheets. BUT, again, I ended up with a bunch of x's in blank cells. L. "Bob Phillips" wrote in message ... John Walkenbach's Excel Formulas book is probably best for what you want? -- HTH RP (remove nothere from the email address if mailing direct) "L/P" wrote in message ... Again, thanks. Is there a good simple to understand book out there I could get that would help me? The HELP menu apparently can't. :-) L. "Bob Phillips" wrote in message ... =IF(C3="","",IF(C3=B3,"p","x")) -- HTH RP (remove nothere from the email address if mailing direct) "L/P" wrote in message ... Here's another problem I can't figure out. I have made a monthly "deposit" sheet with: A B C D E F G H 1 Units $ Due Deposit # 1 Deposit # 2 Deposit # 3 2 Amount Paid Amount Paid Amount Paid 3 1203 $500 4 1204 $600 5 1205 $450 . . 63 1263 $500 64 1264 $450 When a correct deposit is made in Amount (column C,E or G) I want a "P" inserted in column D, F or H. If not correct amount "X". The formula I came up with would be =IF(C3=B3,"p","x"). Problem is that the entire sheet starts out with x's..... Trying to get a blank in columns D, F and H until something is entered in columns C,E or G. Help please, the entire sheet is filled with x's and each unit only makes one deposit a month. L. "L/P" wrote in message ... WOW- thanks. Have a great new year. L. "Jason Morin" wrote in message ... One way: =SUM(COUNTIF(D2:D65,{"p","x"})) HTH Jason Atlanta, GA -----Original Message----- Need to add a column that has either a "P" or an "X". I can figure out how to add "p's" or "x's"( =COUNTIF(D2:D65,"p") but not both ("p and/or x"). It was suggested I use the countif. Suggestions and thanks, L. . |
|
#12
|
|||
|
|||
|
After viewing formula here is "new" formula that does give error msg.
=IF(SUM('January 2005'!C3,'January 2005'!E3,'January 2005'!G3)="","",IF(SUM('January 2005'!C3,'January 2005'!E3,'January 2005'!G3)='January 2005'!B3,"p","x")) Problem is I still get x in blank cells.!@#!! "L/P" wrote in message ... What I'm trying to do is keep cells blank, on the Summary sheet, unless there is something in them. Then check the 3 cells and add the P or X. I tried to use the example from Bob, =IF(C3="","",IF(C3=B3,"p","x")) which does that on a single cell on the January 2005 sheet. I even tried to keep it simple and just test a single cell I know to be blank but then the cell stays blank even when there is a P or X in them!! L. "Tom Ogilvy" wrote in message ... Your first If statement has 4 arguments =if(condition,what to do if true,what to do if false, What to do if ????) You can only have what to do for True or False -- Regards, Tom Ogilvy "L/P" wrote in message ... =IF(SUM('January 2005'!C3,'January 2005'!E3,'January 2005'!G3)='January 2005'!B3,"P","X",IF(SUM('January 2005'!C3,'January 2005'!E3,'January 2005'!G3)='January 2005'!B3,"","")) How can I get around having "too many arguments" error msg? This is a yearly summary sheet(12 columns, one for each month) that would auto add the P or X from the deposit sheets. I started out with: =IF(SUM('January 2005'!C3,'January 2005'!E3,'January 2005'!G3)='January 2005'!B3,"P","X") pulling the P or X for the monthly deposit sheets. BUT, again, I ended up with a bunch of x's in blank cells. L. "Bob Phillips" wrote in message ... John Walkenbach's Excel Formulas book is probably best for what you want? -- HTH RP (remove nothere from the email address if mailing direct) "L/P" wrote in message ... Again, thanks. Is there a good simple to understand book out there I could get that would help me? The HELP menu apparently can't. :-) L. "Bob Phillips" wrote in message ... =IF(C3="","",IF(C3=B3,"p","x")) -- HTH RP (remove nothere from the email address if mailing direct) "L/P" wrote in message ... Here's another problem I can't figure out. I have made a monthly "deposit" sheet with: A B C D E F G H 1 Units $ Due Deposit # 1 Deposit # 2 Deposit # 3 2 Amount Paid Amount Paid Amount Paid 3 1203 $500 4 1204 $600 5 1205 $450 . . 63 1263 $500 64 1264 $450 When a correct deposit is made in Amount (column C,E or G) I want a "P" inserted in column D, F or H. If not correct amount "X". The formula I came up with would be =IF(C3=B3,"p","x"). Problem is that the entire sheet starts out with x's..... Trying to get a blank in columns D, F and H until something is entered in columns C,E or G. Help please, the entire sheet is filled with x's and each unit only makes one deposit a month. L. "L/P" wrote in message ... WOW- thanks. Have a great new year. L. "Jason Morin" wrote in message ... One way: =SUM(COUNTIF(D2:D65,{"p","x"})) HTH Jason Atlanta, GA -----Original Message----- Need to add a column that has either a "P" or an "X". I can figure out how to add "p's" or "x's"( =COUNTIF(D2:D65,"p") but not both ("p and/or x"). It was suggested I use the countif. Suggestions and thanks, L. . |
|
#13
|
|||
|
|||
|
Hard to tell what your actual test should be, but a SUM will never = ""
Perphaps Count('January 2005'!C3,'January 2005'!E3,'January 2005'!G3)=0, "" or SUM('January 2005'!C3,'January 2005'!E3,'January 2005'!G3)=0"", but if all three cells are blank, this will return a zero. Perhaps Trim(January 2005'!C3&'January 2005'!E3&'January 2005'!G3)="","", -- Regards, Tom Ogilvy "L/P" wrote in message ... After viewing formula here is "new" formula that does give error msg. =IF(SUM('January 2005'!C3,'January 2005'!E3,'January 2005'!G3)="","",IF(SUM('January 2005'!C3,'January 2005'!E3,'January 2005'!G3)='January 2005'!B3,"p","x")) Problem is I still get x in blank cells.!@#!! "L/P" wrote in message ... What I'm trying to do is keep cells blank, on the Summary sheet, unless there is something in them. Then check the 3 cells and add the P or X. I tried to use the example from Bob, =IF(C3="","",IF(C3=B3,"p","x")) which does that on a single cell on the January 2005 sheet. I even tried to keep it simple and just test a single cell I know to be blank but then the cell stays blank even when there is a P or X in them!! L. "Tom Ogilvy" wrote in message ... Your first If statement has 4 arguments =if(condition,what to do if true,what to do if false, What to do if ????) You can only have what to do for True or False -- Regards, Tom Ogilvy "L/P" wrote in message ... =IF(SUM('January 2005'!C3,'January 2005'!E3,'January 2005'!G3)='January 2005'!B3,"P","X",IF(SUM('January 2005'!C3,'January 2005'!E3,'January 2005'!G3)='January 2005'!B3,"","")) How can I get around having "too many arguments" error msg? This is a yearly summary sheet(12 columns, one for each month) that would auto add the P or X from the deposit sheets. I started out with: =IF(SUM('January 2005'!C3,'January 2005'!E3,'January 2005'!G3)='January 2005'!B3,"P","X") pulling the P or X for the monthly deposit sheets. BUT, again, I ended up with a bunch of x's in blank cells. L. "Bob Phillips" wrote in message ... John Walkenbach's Excel Formulas book is probably best for what you want? -- HTH RP (remove nothere from the email address if mailing direct) "L/P" wrote in message ... Again, thanks. Is there a good simple to understand book out there I could get that would help me? The HELP menu apparently can't. :-) L. "Bob Phillips" wrote in message ... =IF(C3="","",IF(C3=B3,"p","x")) -- HTH RP (remove nothere from the email address if mailing direct) "L/P" wrote in message ... Here's another problem I can't figure out. I have made a monthly "deposit" sheet with: A B C D E F G H 1 Units $ Due Deposit # 1 Deposit # 2 Deposit # 3 2 Amount Paid Amount Paid Amount Paid 3 1203 $500 4 1204 $600 5 1205 $450 . . 63 1263 $500 64 1264 $450 When a correct deposit is made in Amount (column C,E or G) I want a "P" inserted in column D, F or H. If not correct amount "X". The formula I came up with would be =IF(C3=B3,"p","x"). Problem is that the entire sheet starts out with x's..... Trying to get a blank in columns D, F and H until something is entered in columns C,E or G. Help please, the entire sheet is filled with x's and each unit only makes one deposit a month. L. "L/P" wrote in message ... WOW- thanks. Have a great new year. L. "Jason Morin" wrote in message ... One way: =SUM(COUNTIF(D2:D65,{"p","x"})) HTH Jason Atlanta, GA -----Original Message----- Need to add a column that has either a "P" or an "X". I can figure out how to add "p's" or "x's"( =COUNTIF(D2:D65,"p") but not both ("p and/or x"). It was suggested I use the countif. Suggestions and thanks, L. . |
|
#14
|
|||
|
|||
|
Let me correct this statement
---------- SUM('January 2005'!C3,'January 2005'!E3,'January 2005'!G3)=0"", but if all three cells are blank, this will return a zero. Perhaps ---------- should be SUM('January 2005'!C3,'January 2005'!E3,'January 2005'!G3)=0,"", if all three cells are blank, sum will return a zero. Perhaps another approach could be -- Regards, Tom Ogilvy "Tom Ogilvy" wrote in message ... Hard to tell what your actual test should be, but a SUM will never = "" Perphaps Count('January 2005'!C3,'January 2005'!E3,'January 2005'!G3)=0, "" or SUM('January 2005'!C3,'January 2005'!E3,'January 2005'!G3)=0"", but if all three cells are blank, this will return a zero. Perhaps Trim(January 2005'!C3&'January 2005'!E3&'January 2005'!G3)="","", -- Regards, Tom Ogilvy "L/P" wrote in message ... After viewing formula here is "new" formula that does give error msg. =IF(SUM('January 2005'!C3,'January 2005'!E3,'January 2005'!G3)="","",IF(SUM('January 2005'!C3,'January 2005'!E3,'January 2005'!G3)='January 2005'!B3,"p","x")) Problem is I still get x in blank cells.!@#!! "L/P" wrote in message ... What I'm trying to do is keep cells blank, on the Summary sheet, unless there is something in them. Then check the 3 cells and add the P or X. I tried to use the example from Bob, =IF(C3="","",IF(C3=B3,"p","x")) which does that on a single cell on the January 2005 sheet. I even tried to keep it simple and just test a single cell I know to be blank but then the cell stays blank even when there is a P or X in them!! L. "Tom Ogilvy" wrote in message ... Your first If statement has 4 arguments =if(condition,what to do if true,what to do if false, What to do if ????) You can only have what to do for True or False -- Regards, Tom Ogilvy "L/P" wrote in message ... =IF(SUM('January 2005'!C3,'January 2005'!E3,'January 2005'!G3)='January 2005'!B3,"P","X",IF(SUM('January 2005'!C3,'January 2005'!E3,'January 2005'!G3)='January 2005'!B3,"","")) How can I get around having "too many arguments" error msg? This is a yearly summary sheet(12 columns, one for each month) that would auto add the P or X from the deposit sheets. I started out with: =IF(SUM('January 2005'!C3,'January 2005'!E3,'January 2005'!G3)='January 2005'!B3,"P","X") pulling the P or X for the monthly deposit sheets. BUT, again, I ended up with a bunch of x's in blank cells. L. "Bob Phillips" wrote in message ... John Walkenbach's Excel Formulas book is probably best for what you want? -- HTH RP (remove nothere from the email address if mailing direct) "L/P" wrote in message ... Again, thanks. Is there a good simple to understand book out there I could get that would help me? The HELP menu apparently can't. :-) L. "Bob Phillips" wrote in message ... =IF(C3="","",IF(C3=B3,"p","x")) -- HTH RP (remove nothere from the email address if mailing direct) "L/P" wrote in message ... Here's another problem I can't figure out. I have made a monthly "deposit" sheet with: A B C D E F G H 1 Units $ Due Deposit # 1 Deposit # 2 Deposit # 3 2 Amount Paid Amount Paid Amount Paid 3 1203 $500 4 1204 $600 5 1205 $450 . . 63 1263 $500 64 1264 $450 When a correct deposit is made in Amount (column C,E or G) I want a "P" inserted in column D, F or H. If not correct amount "X". The formula I came up with would be =IF(C3=B3,"p","x"). Problem is that the entire sheet starts out with x's..... Trying to get a blank in columns D, F and H until something is entered in columns C,E or G. Help please, the entire sheet is filled with x's and each unit only makes one deposit a month. L. "L/P" wrote in message ... WOW- thanks. Have a great new year. L. "Jason Morin" wrote in message ... One way: =SUM(COUNTIF(D2:D65,{"p","x"})) HTH Jason Atlanta, GA -----Original Message----- Need to add a column that has either a "P" or an "X". I can figure out how to add "p's" or "x's"( =COUNTIF(D2:D65,"p") but not both ("p and/or x"). It was suggested I use the countif. Suggestions and thanks, L. . |
|
#15
|
|||
|
|||
|
Thanks, I'll give it a try.
So the problem I'm having is trying to get a SUM to = "" (blank)? "Tom Ogilvy" wrote in message ... Let me correct this statement ---------- SUM('January 2005'!C3,'January 2005'!E3,'January 2005'!G3)=0"", but if all three cells are blank, this will return a zero. Perhaps ---------- should be SUM('January 2005'!C3,'January 2005'!E3,'January 2005'!G3)=0,"", if all three cells are blank, sum will return a zero. Perhaps another approach could be -- Regards, Tom Ogilvy "Tom Ogilvy" wrote in message ... Hard to tell what your actual test should be, but a SUM will never = "" Perphaps Count('January 2005'!C3,'January 2005'!E3,'January 2005'!G3)=0, "" or SUM('January 2005'!C3,'January 2005'!E3,'January 2005'!G3)=0"", but if all three cells are blank, this will return a zero. Perhaps Trim(January 2005'!C3&'January 2005'!E3&'January 2005'!G3)="","", -- Regards, Tom Ogilvy "L/P" wrote in message ... After viewing formula here is "new" formula that does give error msg. =IF(SUM('January 2005'!C3,'January 2005'!E3,'January 2005'!G3)="","",IF(SUM('January 2005'!C3,'January 2005'!E3,'January 2005'!G3)='January 2005'!B3,"p","x")) Problem is I still get x in blank cells.!@#!! "L/P" wrote in message ... What I'm trying to do is keep cells blank, on the Summary sheet, unless there is something in them. Then check the 3 cells and add the P or X. I tried to use the example from Bob, =IF(C3="","",IF(C3=B3,"p","x")) which does that on a single cell on the January 2005 sheet. I even tried to keep it simple and just test a single cell I know to be blank but then the cell stays blank even when there is a P or X in them!! L. "Tom Ogilvy" wrote in message ... Your first If statement has 4 arguments =if(condition,what to do if true,what to do if false, What to do if ????) You can only have what to do for True or False -- Regards, Tom Ogilvy "L/P" wrote in message ... =IF(SUM('January 2005'!C3,'January 2005'!E3,'January 2005'!G3)='January 2005'!B3,"P","X",IF(SUM('January 2005'!C3,'January 2005'!E3,'January 2005'!G3)='January 2005'!B3,"","")) How can I get around having "too many arguments" error msg? This is a yearly summary sheet(12 columns, one for each month) that would auto add the P or X from the deposit sheets. I started out with: =IF(SUM('January 2005'!C3,'January 2005'!E3,'January 2005'!G3)='January 2005'!B3,"P","X") pulling the P or X for the monthly deposit sheets. BUT, again, I ended up with a bunch of x's in blank cells. L. "Bob Phillips" wrote in message ... John Walkenbach's Excel Formulas book is probably best for what you want? -- HTH RP (remove nothere from the email address if mailing direct) "L/P" wrote in message ... Again, thanks. Is there a good simple to understand book out there I could get that would help me? The HELP menu apparently can't. :-) L. "Bob Phillips" wrote in message ... =IF(C3="","",IF(C3=B3,"p","x")) -- HTH RP (remove nothere from the email address if mailing direct) "L/P" wrote in message ... Here's another problem I can't figure out. I have made a monthly "deposit" sheet with: A B C D E F G H 1 Units $ Due Deposit # 1 Deposit # 2 Deposit # 3 2 Amount Paid Amount Paid Amount Paid 3 1203 $500 4 1204 $600 5 1205 $450 . . 63 1263 $500 64 1264 $450 When a correct deposit is made in Amount (column C,E or G) I want a "P" inserted in column D, F or H. If not correct amount "X". The formula I came up with would be =IF(C3=B3,"p","x"). Problem is that the entire sheet starts out with x's..... Trying to get a blank in columns D, F and H until something is entered in columns C,E or G. Help please, the entire sheet is filled with x's and each unit only makes one deposit a month. L. "L/P" wrote in message ... WOW- thanks. Have a great new year. L. "Jason Morin" wrote in message ... One way: =SUM(COUNTIF(D2:D65,{"p","x"})) HTH Jason Atlanta, GA -----Original Message----- Need to add a column that has either a "P" or an "X". I can figure out how to add "p's" or "x's"( =COUNTIF(D2:D65,"p") but not both ("p and/or x"). It was suggested I use the countif. Suggestions and thanks, L. . |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| countif statement | Excel Worksheet Functions | |||
| Countif - Countif | Excel Worksheet Functions | |||
| Countif, Sumif, If - help! | Excel Worksheet Functions | |||
| countif, again | Excel Worksheet Functions | |||
| combining countif formulas | Excel Worksheet Functions | |||
Linear Mode
