I need help with an IF function!

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a spreadsheet with monthly invoices, and I have a summary page that needs to include one cell that shows which months we were billed, for example if we were billed quarterly, I would like the cell to show "1,4,8,12

I'm trying to have my formula look to see if there was a charge in a certain month, and if so, display the appropriate number (ie. 1 for Jan.

I've tried using IF statements, If B1 is my cell representing charges for January

=IF(B1>=1,"1",&IF(C1>=1,"2", &IF(D1>=1,"3",))

Which retrieves "1,2,3" if there is data in cells B1, C1, and D1. However, if there is no data in C1, I get "1,FALSE"

If I tr

=IF(B1>=1,"1",""&IF(C1>=1,"2", ""&IF(D1>=1,"3",""))

and there is no data in B1, but there is data in D1, then I see "1" when I would like to see "1,3

Does anyone have any ideas

Thanks
 
How about

=SUBSTITUTE((B1>1)*1&","&(C1>1)*2&","&(D1>1)*3&","&(E1>1)*4,",0","")

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

TG said:
I have a spreadsheet with monthly invoices, and I have a summary page that
Click to expand...
needs to include one cell that shows which months we were billed, for
example if we were billed quarterly, I would like the cell to show
"1,4,8,12"
I'm trying to have my formula look to see if there was a charge in a
Click to expand...
certain month, and if so, display the appropriate number (ie. 1 for Jan.)
I've tried using IF statements, If B1 is my cell representing charges for January:

=IF(B1>=1,"1",&IF(C1>=1,"2", &IF(D1>=1,"3",)))

Which retrieves "1,2,3" if there is data in cells B1, C1, and D1.
Click to expand...
However, if there is no data in C1, I get "1,FALSE"
 
Back
Top