SUMIFS with date range and four criteria

Hi Friends,

I have done coding to sumif based date with cross checking of two values.

I unable sumif the value based other criteria

So, how to consolidate the value based,

Column-D(Buyer Name) & Column-Q(Qty Status) & Column-K(Order Qt Qty) with Date Range = Column-B(Order Qty(Next Sheet))

Column-D(Buyer Name) & Column-Q(Qty Status) & Column-M(Partial Qty)+Column-O(Shipped Qty) with Date Range = Column-C(Short Qty(Next Sheet))

Column-D(Buyer Name) & Column-Q(Qty Status) & Column-K(Order Qt Qty) with Date Range = Column-D(Order Qty(Next Sheet))

Column-D(Buyer Name) with Column-Q(Qty Range) & Column-M(Partial Qty)+Column-O(Shipped Qty) with Date Range = Column-E(Full Qty(Next Sheet))

Column-D(Buyer Name) & Column-Q(Qty Status) & Column-K(Order Qt Qty) with Date Range = Column-F(Order Qty(Next Sheet))

Column-D(Buyer Name) with Column-Q(Qty Range) & Column-M(Partial Qty)+Column-O(Shipped Qty) with Date Range = Column-G(Excess Qty(Next Sheet))

Here I enclosed screen shot and coding sheet

Thanks
Short & Excess Qty - 1.jpg
Short & Excess Qty - 2.jpg
Short & Excess Qty Details.xlsm

Hello nandhavnk,

Can you post an example of the "Temp" sheet for a single company?

Which version of Excel are you using now? Still Excel 2007?

Hi AligW,

I m using Excel 2016 version.

Thanks

In that case, please update your forum profile. Thanks.

Administrative Note:

Please don't quote whole posts, especially when you are responding to the one immediately preceding your own - it's just clutter. It's OK to quote if you are responding to a post out of sequence, but limit quoted content to a few relevant lines that makes clear to whom and what you are responding. Thanks!

For normal conversational replies, try using the QUICK REPLY box below.

Hello nandhavnk,

Your attachments can be opened. Please try again in your next post.

Hello Leith Ross,

Again I tried some changes myself in code but still not working.

Enclosed coding and working excel
Short & Excess Qty Details.xlsm

Please Login or Register  to view this content.

You do not need code tags round every line - just at the start and the end of the code snippet. I have amended it for you this time, but please don't do it again. All you need to do is select the entire code snippet and click once on the # icon in the toolbar - that's it.

I changed my coding as u said.

If possible to bring the result as i wrote in my coding.

I think you need to elaborate on what you want: there have been lots of views of this thread, but no help offered yet, so this means that you need to give more detail.

Continue here: do NOT start a new thread. Thanks.

I understood my coding can’t understand clearly. Here I am explaining little elaborate
My variables defined as below and coding for sumif names & values

Please Login or Register  to view this content.

What I need in new sheet as in below and respective to understand easily.
Column-D(Buyer Name) & Column-Q(Qty Status) & Column-K(Order Qt Qty) with Date Range = Column-B(Order Qty(Next Sheet))

Please Login or Register  to view this content.

Column-D(Buyer Name) & Column-Q(Qty Status) & Column-M(Partial Qty)+Column-O(Shipped Qty) with Date Range = Column-C(Short Qty(Next Sheet))

Please Login or Register  to view this content.

Column-D(Buyer Name) & Column-Q(Qty Status) & Column-K(Order Qt Qty) with Date Range = Column-D(Order Qty(Next Sheet))

Please Login or Register  to view this content.

Column-D(Buyer Name) with Column-Q(Qty Range) & Column-M(Partial Qty)+Column-O(Shipped Qty) with Date Range = Column-E(Full Qty(Next Sheet))

Please Login or Register  to view this content.

Column-D(Buyer Name) & Column-Q(Qty Status) & Column-K(Order Qt Qty) with Date Range = Column-F(Order Qty(Next Sheet))

Please Login or Register  to view this content.

Column-D(Buyer Name) with Column-Q(Qty Range) & Column-M(Partial Qty)+Column-O(Shipped Qty) with Date Range = Column-G(Excess Qty(Next Sheet))

Please Login or Register  to view this content.

Enclosed screen shot of sum values with two main criteria
Short & Exces Qty - 03.jpg
My Coding Sheet attached
Short & Excess Qty Details.xlsm

Hello,
find attached functioning code to your task. It is not the solution you want but with code you should be able to finish your task.

Please Login or Register  to view this content.

Best Regards,
Filip





If you wish, you can reward me by clicking on my reputation star bottom left.

Administrative Note:

You need to include code tags around your code.

Please take a moment to add the tags. Posting code between [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] tags makes your code much easier to read and copy for testing, and it also maintains VBA formatting.

Please see Forum Rule #2 about code tags and adjust accordingly. Click on Edit to open your post, then highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here

(Note: this change is not optional.)

Also, please change the text colour - change it back to black for legibility, please.

Hi Filip,

Thanks for your reply.

What I have done in my coding and what expected in my coding is...

When I select the month in Cell S1 and clicking "Update" Button. Output result will bring in next sheet with sheet name of selected month in Cell S1 which I already format created Sheet as “Temp”
The specific month range sum give the result to me.
In my coding the below i have done
1. “Temp” Sheet coping and name changed to selected month
2. Column-D Coping and removing duplicates and sorting in alphabet order
I can’t to find the correct code to the below
1. Sumifs the values in “Shipment” Sheet with Sum Range(Order Qty in Column-K), Main Criteria Range-1(Buyer Name in Column-D), Criteria-1(Formatted Sheet(ie with Month Name) in Column(A:A), Main Criteria Range-2(Qty Status in Column-Q), Criteria-2(Formatted Sheet(ie with Month Name) in Cell(B1), Start date & End Date with Date range(F.I.Date Column-B)
2. Sumifs the values in “Shipment” Sheet with Sum Range(Partial Shipped Qty in Column-M)+Sum Range(Shipped Qty in Column-O), Main Criteria Range-1(Buyer Name in Column-D), Criteria-1(Formatted Sheet(ie with Month Name) in Column(A:A), Main Criteria Range-2(Qty Status in Column-Q), Criteria-2(Formatted Sheet(ie with Month Name) in Cell(B1), Start date & End Date with Date range(F.I.Date Column-B)

This output will come in Formatted Sheet(ie with month name sheet) in the column of B & C

And for Column D & E output with Criteria-2(Formatted Sheet(ie with Month Name) in Cell(D1)

And for Column F & G output with Criteria-2(Formated Sheet(ie with Month Name) in Cell(F1)

I guess myself error may come this area...

Please Login or Register  to view this content.

Main Criteria-1 & 2 Ranges from Sourcesheet what i would expect in from this sheet
Attachment 633337

Hi Filip,

Thanks for your reply.

What I have done in my coding and what expected in my coding is...

When I select the month in Cell S1 and clicking "Update" Button. Output result will bring in next sheet with sheet name of selected month in Cell S1 which I already format created Sheet as “Temp”

The specific month range sum give the result to me.

In my coding the below i have done
1. “Temp” Sheet coping and name changed to selected month
2. Column-D Coping and removing duplicates and sorting in alphabet order

I can’t to find the correct code to the below
1. Sumifs the values in “Shipment” Sheet with Sum Range(Order Qty in Column-K), Main Criteria Range-1(Buyer Name in Column-D), Criteria-1(Formatted Sheet(ie with Month Name) in Column(A:A), Main Criteria Range-2(Qty Status in Column-Q), Criteria-2(Formatted Sheet(ie with Month Name) in Cell(B1), Start date & End Date with Date range(F.I.Date Column-B)
2. Sumifs the values in “Shipment” Sheet with Sum Range(Partial Shipped Qty in Column-M)+Sum Range(Shipped Qty in Column-O), Main Criteria Range-1(Buyer Name in Column-D), Criteria-1(Formatted Sheet(ie with Month Name) in Column(A:A), Main Criteria Range-2(Qty Status in Column-Q), Criteria-2(Formatted Sheet(ie with Month Name) in Cell(B1), Start date & End Date with Date range(F.I.Date Column-B)

This output will come in Formatted Sheet(ie with month name sheet) in the column of B & C

And for Column D & E output with Criteria-2(Formatted Sheet(ie with Month Name) in Cell(D1)

And for Column F & G output with Criteria-2(Formated Sheet(ie with Month Name) in Cell(F1)

Hope this will make understandable.

Please Login or Register  to view this content.

Main Criteria 1 & 2 and range selection from Sourcesheet as in screenshot
Short & Excess Qty - 4.jpg

Hi all,

Is possible to get result in my coding or want change it.

Can anyone suggest me.