Nested If Statements Based on an Entire Column

[DaisyGarden]

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

 

[DRSteele]

It sounds to me that you're attempting to count things conditionally. That sounds like a job for the functions COUNTIF and COUNTIFS. Watching this video might help you, as might others from that author: https://www.youtube.com/watch?v=ohGgZ5ik4Xo

 

[DaisyGarden]

I'll watch and give it a go tomorrow and let you know if it works.
Thank you!
Daisy

 

[DaisyGarden]

I watched the video and followed the countifs instruction and it worked!
Thanks!

 

[DRSteele]

I'm so glad your project is progressing.