DaisyGarden
New Member
- Joined
- Jun 9, 2015
- Messages
- 3
Hi -
Can nested if statements reference entire columns or just individual cells.
I am trying to count the number of new clients by month. I have a column A with all clients over a 2 year period - 201401, 201402, 201403 etc.
With each month we get more clients; however, there are repeat clients as well.
I just want to monitor how many new ones we get as the months go by.
I created a column of clients removing duplicates. Then I created row headings 201401, 201402, 201403 etc.
I wanted to create a formula with nested if statements that as it went across the month rows it would only count the number of new clients. I was trying to have the formula enter a "0" until the month where the client first appeared then enter a "1" when it first appeared and then a "0" in the subsequent months. I tried =IF(AND($I:$I=$AN2,$B:$B=$AQ$1,$AP2<>1),1,0) however, when I check the formula dependents instead of referencing I:I it referenced I2.
I did try using count (because I saw it was recommended when I searched the forum) but couldn't get it to count only the first time a clients name appeared.
I hope this isn't too confusing!
I can also send a spreadsheet if it's easier
Thank you!
Daisy
Can nested if statements reference entire columns or just individual cells.
I am trying to count the number of new clients by month. I have a column A with all clients over a 2 year period - 201401, 201402, 201403 etc.
With each month we get more clients; however, there are repeat clients as well.
I just want to monitor how many new ones we get as the months go by.
I created a column of clients removing duplicates. Then I created row headings 201401, 201402, 201403 etc.
I wanted to create a formula with nested if statements that as it went across the month rows it would only count the number of new clients. I was trying to have the formula enter a "0" until the month where the client first appeared then enter a "1" when it first appeared and then a "0" in the subsequent months. I tried =IF(AND($I:$I=$AN2,$B:$B=$AQ$1,$AP2<>1),1,0) however, when I check the formula dependents instead of referencing I:I it referenced I2.
I did try using count (because I saw it was recommended when I searched the forum) but couldn't get it to count only the first time a clients name appeared.
I hope this isn't too confusing!
I can also send a spreadsheet if it's easier
Thank you!
Daisy