SumProduct based on date range sheet qry column C, based on qry column E >0

[RHISM]

Hi all,

I'm trying to sum billing time on sheet named qry column D based on a provider sheet info A1, based on date range sheet qry column C, based on qry column E >0

In the actual Excel book, sheet qry would have multiple months/years but I just put in April in attached

I tried different scenarios with the function sumproduct but kept getting value as an answer. I then tried going piece by piece and stopped after I got this.

if I sum sheet qry column d I get a total of 152.75, if I use sumproduct(qry!D:D) the total doubles.

I have attached a workbook that cooresponds to what i am trying to do.

Is there a specific order I have to do the array in? example =SUMPRODUCT(--(Qry!D:D),--(Qry!B:B=Info!A1)) and then tried adding in date range info-it all failed

Thanks in advance for any assistance

[AliGW]

Administrative Note:

Welcome to the forum.

We would very much like to help you with your query, however the thread title does not really convey what your request is about. Tell us what you are trying to do, not how you think it should be done.

Please take a moment to amend your thread title. Make sure that the title properly explains your request. Your title should be explicit and not be generic (this includes function names used without an indication of what you are trying to achieve).

Please see Forum Rule #1 about proper thread titles and adjust accordingly. To edit the thread title, open the original post to edit and then click on Go Advanced (bottom right) to access the area where you can edit your title.

(Note: this change is not optional. As you are new here, I have done it for you this time.)

[RHISM]

Thank you for Rule#1 info and for changing the title for me. I will me better next time

[JeteMc]

Try pasting the following into cell L2: =SUMPRODUCT((Qry!D2:D176)*(Qry!B2:B176=A1))
To get a formula that can be dragged across row 2 change the months in row 1 to dates (format mmm), change the dates on the Qry sheet to actual dates instead of text (Text to Columns > Finish) and use:

Please Login or Register  to view this content.

Note that converting the range on the Qry sheet into a table allows the formula to adjust as rows are added to the end of the table.
You could also get results from a pivot table.
Let us know if you have any questions.

[RHISM]

JeteMc-
Thank you for your reply and assistance. I replicated what you suggested on my production book and it works perfect.
I tested your suggestion by making the data I'm receiving static.
I receive this info from a SQL linked qry, so I am asking DBA to adjust the format he is sending to me.
I will update this thread again once I have the new linked qry but it worked perfect with static data.

One last thing if I may- is there any a reason why you used * in the array and not -- ?

[davsth]

Unless you get very techy * and -- essentially do the same thing

[JeteMc]

@davsth, Thank You for the explanation. I am not techy myself, * is just the way that I learned and have always written my SUMPRODUCT based formulas.
@RHISM, You're Welcome and thank you for the feedback. If the proposal works after linking the new query, please take a moment to mark the thread as 'Solved' using the thread tools menu above your first post.
I hope that you both have a blessed day.