Comparing Dates to see if they are in the same month and then adding them

[BoggleJ]

So for my work I am trying to make an Excel spreadsheet for my sales job. We get paid based on the number of closings in the month from previous sales, and our pay tier is based on the number of sales in the same month we get of closings. So I am trying to compare the date that we first spoke with a client to when we got the sale and add each one that matches in 1 formula. This formula is as close as I got and it only returns the 1 value =IF(AND(YEAR(B7:B25000)=YEAR(P7:P25000),MONTH(B7:B25000)=MONTH(P7:P25000)),1,0). I have tried adding SUM to it to see if that works and I feel like I am either doing it wrong i tried this =IF(SUM(AND(YEAR(B7:B25000)=YEAR(P7:P25000),MONTH(B7:B25000)=MONTH(P7:P25000)),1,0)). Can someone help me out with this?

 

[BoggleJ]

I am also looking for help with a formula to compare the months to see if they are in the same month, but it will add the values of cells in the same row so I can make a client tracker that automatically calculates commision.

 

[EFANYoutube]

So for my work I am trying to make an Excel spreadsheet for my sales job. We get paid based on the number of closings in the month from previous sales, and our pay tier is based on the number of sales in the same month we get of closings. So I am trying to compare the date that we first spoke with a client to when we got the sale and add each one that matches in 1 formula. This formula is as close as I got and it only returns the 1 value =IF(AND(YEAR(B7:B25000)=YEAR(P7:P25000),MONTH(B7:B25000)=MONTH(P7:P25000)),1,0). I have tried adding SUM to it to see if that works and I feel like I am either doing it wrong i tried this =IF(SUM(AND(YEAR(B7:B25000)=YEAR(P7:P25000),MONTH(B7:B25000)=MONTH(P7:P25000)),1,0)). Can someone help me out with this?

This confusing because I'm not sure how your data is set out, are all closings in the B column and all Sales in the P column, If so how do you know what closing is for what sale?
Or is it 1 closing but then why do you need to reference so many in B column

 

[BoggleJ]

Column B is the date I first speak to the client and Column P is the day the sale was confirmed but i know what to change it to for my date chart for commissions. So it would be changed to =IF(AND(YEAR(AD59)=YEAR(P7:P25000),MONTH(AD59)=MONTH(P7:P25000)),1,0). I have a Column(AD) setup with 1 month intervals from the beginning of this year to 5 years later in AD. This sheet I making is a full Client tracker with all their data regarding the client so that I can call them back when things change or specials come up. I am trying to make an all in one tool for all my coworkers in the office.

 

[EFANYoutube]

Column B is the date I first speak to the client and Column P is the day the sale was confirmed but i know what to change it to for my date chart for commissions. So it would be changed to =IF(AND(YEAR(AD59)=YEAR(P7:P25000),MONTH(AD59)=MONTH(P7:P25000)),1,0). I have a Column(AD) setup with 1 month intervals from the beginning of this year to 5 years later in AD. This sheet I making is a full Client tracker with all their data regarding the client so that I can call them back when things change or specials come up. I am trying to make an all in one tool for all my coworkers in the office.

So to clear it up, in Column B are the more than 1 entry for 1 month?
If so, you must have another column stating the client name? so you know what matches with what?

 

[BoggleJ]

There are about 15-20 full row entries added per day with clients information Column B is the first day I spoke with that client. Column P is the date when the sale is confirmed Column R is the date when the sale is funded which is what counts as a closing. The name of the client is in another column but it shouldn't be needed to calculate for a full months commission which is when we get paid on our sales. The goal I have is to compare the date the sale was finalized with a table of months for the next five years. Add the number of sales finalized in the same month into a cell and then add the Volume of those sales in a cell next to it with another formula. That way I can compare the volume of the sales in the month to the number of sales to determine what tier you will be paid out at for the volume of those sales to calculate commissions just by filling out the client tracker we already use.

 

[BoggleJ]

Here is a visual for better understanding

 

[EFANYoutube]

I think this might be what your after?

=COUNTIFS(B:B,">="&AD6,P:P,"<"&AD7)

 

[BoggleJ]

I think I understand how that works I tried applying it to the table for books on xl4 and for funded and I got it to work for 1 month but I am missing how that works specifically I guess could you break it down for me?

 

[BoggleJ]

Also I would take any suggestions to auto fill all the slots in the table from xl4. If possible Id rather not manually apply a formula for 3 cells per column for 100 rows.