• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

SUMPRODUCT based on two criterias and a date

BigD

Member
Need help before I pull my hair out, please.

If you look at E23 on "Dashboard" tab, I am trying to do a sumIFS based upon D23 and dates on "SP45" tab for each month. As you will see, am I getting "0".

Any help is greatly appreciated.
 

Attachments

  • Crew Delays (2014)_Dustin.xlsx
    75.5 KB · Views: 4
Hi BigD,

With this much information all I can say something like this:

=SUMPRODUCT((Range1=Condition1)*(Range2=Condition2)*(DateRange=Date Condition))

You can change comparative operator as per your requirement.

Else provide a sample file.

Regards,
 
Somendra: I accidentally hit submit before I completed it, sorry. In addition, I ended up trying a SUMIFS without much luck as well.
 
Works beautifully. Now, I just have to teach myself the code. Will it work without the IFERROR because when I take it out, I get an extremely large number?I like the IFERROR putting the "0" versus an error, but the code was easier for me to learn by taking it a part.

Thank you for the help!
 
I copied the exact same formula and changed the table name, and I am getting an error:

Code:
=SUM(NonSP45Delays[Total Hrs. Delayed]*(NonSP45Delays[Craft]=$D39)*(MONTH(E$38)&YEAR(E$38)=IFERROR(MONTH(NonSP45Delays[Date])&YEAR(NonSP45Delays[Date]),0)))

Any ideas on what I could be doing wrong? Also, without the IFERROR, should this work?

Code:
=SUM(NonSP45Delays[Total Hrs. Delayed]*(NonSP45Delays[Craft]=$D39)*(MONTH(E$38)&YEAR(E$38)))
 

Attachments

  • Crew Delays (2014)_Dustin.xlsx
    81 KB · Views: 3
Hello BigD,

Try this

=SUMPRODUCT(NonSP45Delays[Total Hrs. Delayed],(NonSP45Delays[Craft]=$D39)*(TEXT(NonSP45Delays[Date],"yymm")=TEXT(E$38,"yymm")))

Based on file, assuming you are using Excel 2007 or later. SO you can use SUMIFS, which is more faster than SUMPRODUCT.

=SUMIFS(NonSP45Delays[Total Hrs. Delayed],NonSP45Delays[Craft],$D39,NonSP45Delays[Date],">="&EOMONTH(E$38,-1)+1,NonSP45Delays[Date],"<="&EOMONTH(E$38,0))

Copy to E39, then copy to E50. Then copy E39:E50 and paste to other blocks. DO NOT drag across, as this will change Table reference.
 
Still having trouble. See E24 on tab "Formulas". I am getting "#Value!" error.

Code is the following:

Code:
=SUM(SP45Delays[Total Hrs. Delayed]*(SP45Delays[Craft]=$D24)*(MONTH(E$23)&YEAR(E$23)+(NonSP45Delays[Total Hrs. Delayed]*(NonSP45Delays[Craft]=$D24)*(MONTH(E$23)&YEAR(E$23)=IFERROR(MONTH(SP45Delays[Date])&YEAR(SP45Delays[Date]),0)))))

Any advice is appreciated.
 

Attachments

  • Crew Delays (2014)_Dustin.xlsx
    94.1 KB · Views: 5
Hi ,

This formula is different from your initial question, now you are referring to both the sheets in the same formula. Can you explain what is your purpose?

Regards,
 
Hi Dustin,

In the NonSP45 table, the Total hours delayed column has both numbers and text, while the SP45 table's column just had numbers. This is the source of the problem. We can overcome this by using the TEXT function to over-ride the text and replace it with a 0. New formula:

=SUMPRODUCT(SP45Delays[Total Hrs. Delayed]*(SP45Delays[Craft]=$D24)*(TEXT(E$23,"yyyymm")=TEXT(SP45Delays[Date],"yyyymm")))+
SUMPRODUCT(TEXT(NonSP45Delays[Total Hrs. Delayed],"0;0;0;""0""")*(NonSP45Delays[Craft]=$D24)*(TEXT(E$23,"yyyymm")=TEXT(NonSP45Delays[Date],"yyyymm")))
 
Last edited:
BigD,

If you use * for text entries result always will be error. One way to avoid is use "comma" instead of * to sum range.

=SUMPRODUCT(SP45Delays[Total Hrs. Delayed],(SP45Delays[Craft]=$D24)*(TEXT(E$23,"yyyymm")=TEXT(SP45Delays[Date],"yyyymm")))+
SUMPRODUCT(NonSP45Delays[Total Hrs. Delayed]
,(NonSP45Delays[Craft]=$D24)*(TEXT(E$23,"yyyymm")=TEXT(NonSP45Delays[Date],"yyyymm")))

See red highlighted comma after [Total Hrs. Delayed]

As i mentioned in the last post, if you are using Excel 2007 or later SUMIFS will be better.


Luke: AS you know, negative or decimals will not handle with that TEXT (0;0;0;0) --- ('_') ---
 
@Haseeb A , always there to find my mistakes. :p
You are correct, of course. I am curious, have you found a way to use the TEXT function within the SUMIFS function? I have never been able to get it to work together.
 
Works beautifully. Now, I just have to teach myself the code. Will it work without the IFERROR because when I take it out, I get an extremely large number?I like the IFERROR putting the "0" versus an error, but the code was easier for me to learn by taking it a part.

Thank you for the help!

@BigD

Refer above discussion that was the reason of using IFERROR. I never knew that with comma this can be eliminated.

I just got used to * inside SUMPRODUCT because in many cases the array size is not same.

Thanks @Haseeb A for clearing this.

Regards,
 
I have a similar problem, I am using the sumproduct function and trying to create a dynamic lookup tool where I can choose different criteria and it will tell me the amount in each cast, I would also like to be able to leave specific drop down blank, essentially creating a wildcard *. It will make a lot more sense if you look at the attached example I created.
Any suggestions / ideas will be much appreciated. I am happy to try sumif just not sure how it will work.

Thank you.

Michael
 

Attachments

  • Sum product example.xlsx
    11.5 KB · Views: 8
Hi Michaels,

The problem with wild cards is they work on text only. SO if you can convert the year into text format, like I have done in the file attached than SUMIFS can be used as it can handle wild card charters.

Regards,
 

Attachments

  • Sum product example.xlsx
    11.8 KB · Views: 4
Great, thank you Somendra. That works now. Can you use a wild card with a sumproduct formula? Even as text it still wont work. It kind of seems outdated if using SUMIFS.
 
You can add Wildcards to Sumproduct by using the technique shown below
=SUMPRODUCT((IF(C3="",1,H8:H15=C3))*(I8:I15=D3)*(J8:J15=E3)*(K8:K15)) Ctrl+Shift+Enter

This says if the Color is blank use all colors 1, otherwise use the color
It has to be entered as an array formula
 
SUMPRODUCT cannot handle wild cards. SUMIF/SUMIFS/COUNTIF/COUNTIFS/MATCH/SEARCH are some functions which can handle wild cards.

Regards,
 
Or you can use a technique like
=SUMPRODUCT((H8:H15=C3)*(I8:I15=D3)*(LEFT(J8:J15,Len(E3))=TEXT(E3,"##"))*(K8:K15))
Which will accept any cars with the year starting say 201 in cell E3
 
Awesome thanks for your help. Think the sumifs the easiest way to go using the wildcard but all good suggestions.

Thank you
 
Back
Top