Countifs multiple criteria and date has to be >= 15th of the month

Hi,

How would I solve this?

I need to count any rows if they meet multiple criteria, but also the date field has to be greater than or equal to the 15th of the relevant month. E.g. Count if Column labeled contract type says "new build (cash)", and column labeled date of signature is >= 15th of July. Ideally July can 'move' as formula is pulled across each month.

Please help!

Capture.PNG

Welcome to the forum.

Assuming your data are in columns A and F, maybe you try something like this:

=SUMPRODUCT((A2:A100="new build (cash)")*(DAY(F2:F100)>=15))

If that does not work, please follow instructions on yellow banner abobe to upload a sample wb.

Good luck!

Hi!

I have tried, doesnt quite work I think thats the right idea tho!!

i cant attach actual document as it is too big, so have made a very simple version. The formula currently does WIP and Cash but need dates to be included for >= 15th of the month. It needs to be dragged to august and still work.

Please help!!

Thanks!
Lois

Another formula option to paste in F3 and copy across:

Formula:

Please Login or Register  to view this content.

Hi, i think this is exactly the formula i need! although i cant get it to work!! I have attached another doc, i have written what I need in red, i would really appreciate it if you could apply your answer above to my example. / even complete it and attach back as I cant get it to work!!

Thank you!!

The date column in your data tab is confusing, as some cells are text instead of numbers (dates).
To correct that, try selecting the whole range, e.g. H13:H1000, and from the Data ribbon, select Text to Column.
Then in the dialog box that comes up leave the default option (Delimited) and press Next;
Uncheck all delimiters and press Next;
In the last step, select Date (leave MDY selected) and press Finish.

In the attached wb, to count based on dates smaller then 15, I use this formula in U56, copied across:

Formula:

Please Login or Register  to view this content.

And this one in U57, for dates as 15 and later:

Formula:

Please Login or Register  to view this content.

As the heading in row 46 is not Excel friendly for date reference, in the argument DATE(2021,COLUMNS($A$1:J$1),15) in the formulas above, I use COLUMNS($A$1:J$1), which results 10, the month represented in U46 (Year 2021, as I assume it should be).
As the formula is copied (dragged) across to V56, the month argument turns into COLUMNS($A$1:K$1) (11=November), then COLUMNS($A$1:L$1) (12=December), then COLUMNS($A$1:M$1) (13=January of 2022), and so on.
If you have to start count on other column, make sure you change that argument accordingly.

Please check if this will work for you.

Hi,

try this:
AD47:

Please Login or Register  to view this content.

AD48:

Please Login or Register  to view this content.