SUMIF with date criteria

Hi

Looking for some advice on how to develop an existing SUMIF formula which will also consider a specific/fixed date - hope someone can assist!!

Please refer to the attached file (cmck_SUM.xlsx). I have created a SUMIF formula in column F ('Total Certificates') which will sum the quantity of certificates in column B (NoOfCertificates') depending on the 'Supplier' (Column E).

However what I want to do is to exclude any certificates from the total in column F if 'NoOfCertificates' (column B) has a corresponding 'Start Date' (column C) which is before/prior to 01/04/2022. (where the date format is UK, DD/MM/YYYY).

I should point out that the attached file is only a small sample - the file I need to create the formula for has thousands of rows, and columns E and F would probably reside on a different sheet but within the same .xlsx workbook. I've only added those columns here for illustration purposes

Help on this would be very much appreciated!!!

Cal

Are you still using Excel 2010? If not, please update your profile whilst I have a look at this.

You are using UNIQUE, so hopefully you have 365 (update your profile, please), which means you can try this in place of what you already have:

=LET(u,TOCOL(DROP(UNIQUE(A:A),1),1),s,BYROW(u,LAMBDA(r,SUMIFS(B:B,A:A,r,C:C,"<"&DATE(2022,4,1)))),HSTACK(u,s))

Or this to include titles:

=LET(u,TOCOL(DROP(UNIQUE(A:A),1),1),s,BYROW(u,LAMBDA(r,SUMIFS(B:B,A:A,r,C:C,"<"&DATE(2022,4,1)))),t,{"Supplier","Sum"},VSTACK(t,HSTACK(u,s)))

You have to delete EVERYTHING first so that there is room for the formula to spill - open the second workbook I attached. The formula goes into E1 once columns E and F are completely blank.

If that takes care of your original question, please choose Thread Tools from the menu link above and mark this thread as SOLVED. You can also access the SOLVED tag by editing the opening post and choosing SOLVED from the drop-down to the left of the title box.

Also, if you have not already done so, remember that you can reward anyone who offered you help towards a solution for your issue by clicking the small star icon (* Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of each of those who offered help.

OK - try this, then:

=LET(u,TOCOL(DROP(UNIQUE(A:A),1),1),s,BYROW(u,LAMBDA(r,SUMIFS(B:B,A:A,r,C:C,">="&DATE(2022,4,1)))),t,{"Supplier","Sum"},VSTACK(t,HSTACK(u,s)))

If this is what you want, please sign off the thread as explained in post #8.

Thanks for the kind comment.