Adapting an existing project to be more accurate

[ANTHR1CH]

Greetings everyone,

I'm looking for some advice on a project I've been working on for some time. I should mention that the bulk of the work has been done without any 'major' VB knowledge, and has relied heavily on online resources!

The project itself is a fairly simple sales lead and sales conversion tracker that my team of 15 people use to track when they've sold, what they've sold and the date of conversion.

Currently, I'm using a simple =COUNTA(A2:A100) to count the non-blank cells and this total becomes the total number of leads logged during the month. The issue I'm having is that column A simply records the customers account number, so basically the formula looks for the account number column not being blank, and then counts it as a lead if it's not blank.
This is starting to cause an issue when we roll over between months, as a lead may not convert in the same month it was logged. Therefore I need a method of using a date column (already in place, column C) and having the formula only count the lead as a lead if it falls into the current month.

I do have a small VBA Userform that the users must use to "unhide" the next months sheet (we have lots of issues with people breaking sheets, so they're hidden). And there's also an annual summary sheet that copies over the metrics from each monthly sheet onto a one-pager with the agents annual sales stats. Each user has their own sheet, and each sheet is not connected/shared.

Can anyone help me out with the formula I may need to use to have the sheet look at the date column instead? I'm terrible at IF statements


Thank you so much in advance guys!
Anth

[AliGW]

Welcome to the forum.

There are instructions at the top of the page about how to attach a sample workbook.

[Pepe Le Mokko]

Administrative note

Welcome to the forum

missed the yellow banner advising how to get answers faster by posting a sheet ?

Please take a moment to read it and attach a sheet accordingly.

And while you are at it, refresh your memory concerning our Forum Rules, which you probably missed / forgot.

Thanks you for helping us help you

[ANTHR1CH]

I didn't miss the rules? Care to elaborate on how I've broken them please?

[ANTHR1CH]

Welcome to the forum.

There are instructions at the top of the page about how to attach a sample workbook.

That's done, thanks for your help!

[AliGW]

Is it column D you wish to automate? If so, explain your logic, please. If not, where are we meant to be looking?

Excel 2016 (Windows) 32 bit

	A	B	C	D
1	A/C	Type	Date	Converted?
2	1	Payroll	02/01/2020	Yes
3	2	Accounts	02/01/2020	Yes
4	3	Test	31/12/2019	Yes
5	4	Test	01/02/2020	No
6	5	Test	02/02/2020	Yes

Sheet: January 2020

[AliGW]

I didn't miss the rules? Care to elaborate on how I've broken them please?

You didn't break any rules.

Please respond to my questions in post #6. Thanks.

[ANTHR1CH]

Columns O and P from the Metrics side handles the totals from columns A - I

I need the value in P3 to be populated based on the date entered in column C, for example: 01/01/2020 - 31/01/2020 entered, this will +1 to P3. Anything outside of the date range for the current month won't be counted and won't affect the value in P3. for example = 31/12/2019 +0 to P3.

[AliGW]

Sorry, but this doesn't really answer my question. The formula in P3 is this:

=COUNTIF(D2:D100,"Yes")

However, the data does not seem to match what you are saying. Is the data in column D correct? Is it the column D data that you wish to generate to use in the formula, or do you not want to have column D there at all?

[ANTHR1CH]

Formula in P2 is =COUNTIF(D2:D100,"Yes").
Formula in P3 is =COUNTA(A2:A100).

On my sheet anyway...

Data in D is fine, my issue is with P3 "TOTAL LEADS" - I'm wanting to make this formula look at the DATE column (C) and +1 the count if the date range is within the current month. I'm not sure how else I can explain this now haha

Attachment 656252

[AliGW]

OK - let me have another look.

[ANTHR1CH]

Thank you!

[AliGW]

Try this:

=COUNTIFS(A2:A100,"<>",C2:C100,">="&EOMONTH(TODAY(),-1)+1,C2:C100,"<="&EOMONTH(TODAY(),0))

[ANTHR1CH]

OMG - You HERO. Thank you so much, that works perfectly! I'm very appreciative of your help

[AliGW]

You're welcome.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

[ANTHR1CH]

That's done! Thanks again!