CODEWITHSUNDEEP
excelexcel-formula
Louis Down
Louis Down

Reputation: 51

Count rows where date is inside current month with multiple criteria

I'm trying to create a formula that checks the date column of a row and checks it is within the current month, then checks a separate column in a row and checks it is equal to some text, the formula would then output a count of instances where that is true.

For reference, the columns are named as two ranges, LeadDate and LeadAcquisitionChannel.

[edit] The Refers to: definitions are:

'LeadDate Refers to:
=OFFSET('Enquiry Log'!$A$2,0,0,COUNTA('Enquiry Log'!$A:$A),1) 'LeadAcquisitionChannel Refers to:
=OFFSET('Enquiry Log'!$J$2,0,0,COUNTA('Enquiry Log'!$J:$J),1)

I ran the date check with following formula:

=COUNTIF(LeadDate,">"&EOMONTH(TODAY(),-1)

Which was successful in telling me how many entries fall within the current month.

So I assumed I could then add the multiple criteria in a COUNTIFS like this:

=COUNTIFS(LeadDate,">"&EOMONTH(TODAY(),-1),LeadAcquisitionChannel,B27)

B27 being a cell containing the text I want to check for.

This formula returns me a #VALUE! error.

Any ideas as to why this is happening or whether there is an alternative method I can use?

Upvotes: 2

Views: 291

Answers (1)

user4039065
user4039065

Reputation:

The key here lies in the Refers to: definitions for both LeadDate and LeadAcquisitionChannel as COUNTIFS requires that each criteria range be the same size and shape of cells.

For a dynamic list of dates that grows when new dates are added:

='Enquiry Log'!$a$2:index('Enquiry Log'!$a:$a, match(1e99, 'Enquiry Log'!$a:$a))

For a matching dynamic list of other information in an adjacent column use the same terminating row like this:

='Enquiry Log'!$j$2:index('Enquiry Log'!$j:$j, match(1e99, 'Enquiry Log'!$a:$a))

Each of these refers to a column of data extending down in each individual column to the row containing the last date in column A.

Your original relied on individual COUNTA counts to reshape OFFSET. There is no guarantee that there isn't an interim blank row or that they each continue down to the same row. You could homogenize the originals with,

=OFFSET('Enquiry Log'!$J$2, 0, 0, COUNTA('Enquiry Log'!$A:$A), 1)

... but that still doesn't guarantee that interim blank cells in column A wouldn't skew results.

Upvotes: 2

Related Questions