Help with sumif to count based on date

[sipa]

I'm trying to count by product type to todays day / month, ie 15 Jan ignoring the year.

I have the formula below that counts by type but to 15 jan 2016 which is not what I need. It just needs to read todays date minus the year and apply it to the count.

Sorry, posted wrong formula before, this is the one I'm currently using

=SUMPRODUCT(('QOrders'!$F$9:$F12)*('QOrders'!$E$9:$E12<=TODAY())*('QOrders'!$J$9:$J12="SQ"))

Many thanks

[Glenn Kennedy]

It would be easier to follow with a sample sheet. can you attach one?

[hemesh]

@ sipa ,may be
=SUMPRODUCT((TEXT(A1:E1,"DD")=TEXT(TODAY(),"DD"))*A2:E2) for only day match


=SUMPRODUCT((TEXT(A1:E1,"DDMM")=TEXT(TODAY(),"DDMM"))*A2:E2) for day and month match

[Khalidngo]

Hi sipa,

Can you give it a try:
=SUMPRODUCT((TEXT(A1:E1,"dd-mm")<=TEXT(TODAY(),"dd-mm"))*(A2:E2))

Regards,

[Khalidngo]

Oops, same already shared by hemesh.

[hemesh]

use below
=SUMPRODUCT(('QOrders'!$F$9:$F12)*(TEXT(QOrders!$E$9:$E12,"DDMM")=TEXT(TODAY(),"DDMM"))*('QOrders'!$J$9:$J12="SQ"))

[sipa]

Thanks for the reply's

The formula I originally posted was wrong, sorry. Please see the corrected one above.

[hemesh]

try the last one in case that does not solve your issue then upload a sample book

[sipa]

Hi Hemesh
Thanks for the revised formula.
I can see that it should work, but unfortunately it's not returning the correct value of 3, just a zero. any ideas ?

regards

[hemesh]

below will check month and days
=SUMPRODUCT(('QOrders'!$F$9:$F12)*(TEXT(QOrders!$E$9:$E12,"DDMM")=TEXT(TODAY(),"DDMM"))*('QOrders'!$J$9:$J12="SQ"))

below will check days only
=SUMPRODUCT(('QOrders'!$F$9:$F12)*(TEXT(QOrders!$E$9:$E12,"DD")=TEXT(TODAY(),"DD"))*('QOrders'!$J$9:$J12="SQ"))

[sipa]

As requested I've uploaded a sample workbook.

contains data sample and required output.

thanks

[Glenn Kennedy]

Are you aware that you have 2016 dates in the columns headed 2013/2014 and 2014/2015??? Is that intentional?

[sipa]

No, my mistake. I just quickly cobbled something together to show what I'm looking for.

The dates should be as per the header.

[hemesh]

results you are expecting is correct?

[sipa]

Yes, the output in the sample is correct, count for each type until & including todays date, jan 15

count from beginning date shown

[Glenn Kennedy]

OK. I have changed all the incorrect dates to (what I believe to be) corrrect dates. I also rearranged your anticpated results a little, to make it easier for you to maintain the formulae in the future. this was th ebasic formula that I sed:
=SUMPRODUCT((Data!$C$3:$K$21=$C$4)*(Data!$A$3:$I$21>=Count!B6)*(Data!$A$3:$I$21<=TODAY()))

See it in context in the sheet.

[Glenn Kennedy]

Yes, the output in the sample is correct, count for each type until & including todays date, jan 15

count from beginning date shown

Really??? How on earth do you get those values??? back to the drawing board!!!

[Glenn Kennedy]

How on earth do you get 12 "ow"s in 2016 to date?

[hemesh]

still not getting the same can you please upload a correct sample file.

[sipa]

Ok, Here is another sample file with corrected dates and some notation to show how I'm getting my counts.
Hope this helps and I appreciate your persistence.

Regards

[hemesh]

in data sheet D24 try below
=SUMIFS(B3:B21,A3:A21,"<="&DATEVALUE(TEXT(TODAY(),"DD-mm-")&"2014"),C3:C21,C24)
change the references

going out,in an hour i will be back and give complete soltion

[hemesh]

hello try on C15 where you are expecting results
=SUMIFS(Data!$B$3:$B$21,Data!$A$3:$A$21,"<="&DATEVALUE(TEXT(TODAY(),"DD-MM-")&RIGHT(Count!A15,2)),Data!$C$3:$C$21,Count!C$13) and drag to right

change the references to make it work with 2014/15 and 2015/16

[newdoverman]

Using your last upload and assuming that the < signs indicate what you want summed, this will sum the values for OW for the months October and November 2013/2014

Please Login or Register  to view this content.

This will sum SQ for the months September, October, November, January.

Please Login or Register  to view this content.

Here is your workbook with the formulae filled in where you indicate

[sipa]

Hemesh, your solution =SUMIFS(B3:B21,A3:A21,"<="&DATEVALUE(TEXT(TODAY(),"DD-mm-")&"2014"),C3:C21,C24) Seems to be giving me the correct solution, thank you.

Glen, thanks for your input, if you have an alternative solution I'd like to see it if possible.

Newdoverman, thanks for your contribution, although it's not as fully automated as that by Hemesh.

Best Regards

[hemesh]

@ sipa, you are welcome and thanks for your kind words.
If solutions provided here have helped you then you can mark the thread as solved.

[sipa]

I have run into a slight issue with this solution =SUMIFS(B3:B21,A3:A21,"<="&DATEVALUE(TEXT(TODAY(),"DD-mm-")&"2014"),C3:C21,C24)

I'm using this type of formula against data in another closed workbook, this is returning #value! , whereas it returns the correct result if the source workbook is open.
I have read that a formula using sumproduct using the full path to the closed workbook instead of sumifs can access a closed workbook successfully. Could anyone recode the above formula using sumproduct for me please ?

[newdoverman]

The solution that I gave you in msg#23 uses SUMPRODUCT. Try adapting that to your actual situation.

SUMIFS, I think requires open workbooks. SUMIF in all versions of Excel has this problem so I think that SUMIFS probably does too.

[hemesh]

in case of sumproduct you can use below in sheet 2 c15
=SUMPRODUCT((Data!$B$3:$B$21)*(Data!$A$3:$A$21<=DATEVALUE(TEXT(TODAY(),"dd-mm-")&RIGHT($A15,2))*(Data!$C$3:$C$21=C$13)))

[Glenn Kennedy]

OK. I think that this is it. One formula for the lot. I needed to remove a space in 2013 /2014 to give 2013/14 in "Count": to match the column headers in "Data" and the blank rows in your desired result.

Please Login or Register  to view this content.

Thanks to Hemesh for making me realise that if it could work for one dataset, it could be made to work for them all

[sipa]

Hemesh, thanks for your updated formula which is working just fine.
Using Sumproduct with the full path to the closed workbook does indeed work, avoiding the nasty #Value! error.

Glen, thanks also for your solution, I'm going to test them both and see which I prefer. I will update here to give further feedback.

thanks again to everybody who helped.

[hemesh]

@ Sipa ! You are welcome, Thanks for feedback and I Think Glenn Have given you something more powerful to work