SUMIF function with multiple criteria from a data list to get orders and returns!

Originally Posted by jrich16

Formula:

Please Login or Register  to view this content.

So returns should be:

Formula:

Please Login or Register  to view this content.

What or where is Sheet2! that was mentioned in the formulas? Is it Data

Also, according to the Data sheet, there are 4 entries for Return, which is not going with the result you want.

Originally Posted by azumi

Sheet2! is no there

I am afraid that I do not understand, you mean it does not exist?

1. Why have you created a new account?
2. Please fill in your Excel version in your new account.

You said that this formula works: =SUMIFS(Data!$G:$G,Data!$D:$D,"Order",Data!$J:$J,A6,Data!$V:$V,"=>10/1/2023"). It returns 0, but your expected result is 6. This is because none of the dates in your workbook are real dates - they are text.

Try this:

=SUMPRODUCT((Data!D:D="Order")*(Data!J:J=A6)*IFERROR((Data!V:V+0>=C6+0),1),Data!G:G)

and this:

=SUMPRODUCT((Data!D:D="Return")*(Data!J:J=A6)*IFERROR((Data!V:V+0>=C6+0),1),Data!G:G)

There are 4 returns that meet the criteria, bu the way, not just 1.

EXCEPT NEED TO REMOVE ANY SERIALS NUMBERS THAT HAS AN ORDER (Z5LF) BEFORE THE START DATE OF 10/1/2023

There is no ZSLF anywhere in the sample workbook that I can see.

In your data sheet date are text. Convert into date format.

Formula:

Please Login or Register  to view this content.

Formula:

Please Login or Register  to view this content.

In D4 in Need Help sheet for Orders:

Formula:

Please Login or Register  to view this content.

Result is 6.

In E4 in Need Help sheet for Returns:

Formula:

Please Login or Register  to view this content.

Result is 4.


In E6 in the Need Help sheet you typed 1, is this by mistake, or it is the result you are looking for? or this due to the note you mentioned

EXCEPT NEED TO REMOVE ANY SERIALS NUMBERS THAT HAS AN ORDER (Z5LF) BEFORE THE START DATE OF 10/1/2023

Also, what is the meaning of the (Z5LF)?

Hi,

First of all thank you so much for helping me on: excelforum.com/excel-formulas-and-functions/1420354-sumif-function-with-multiple-criteria-from-a-data-list-to-get-orders-and-returns.html#post5926502

thank you SO MUCH THIS WORKS but it fails when looking at the "order" dates for the first 2 line items which affect the return date as well for the third line item and last line item

but

Here are the 4 items that would not be included in the summary tab/results:

1. first order on the data list is 10/1/2022 =- before the start date
2. second order on the data list is before start date
3. first return is dated after the start date BUT is for an order that occurred before the start date, serial number 1
4. the last line item is for return with serial number "0", which is for an order before the start date

therefore the results should not include 2 returns and 2 orders since all orders must occur after the start date of 10/1/2023 AND for returns, the returns must be for a serial number order that occurred after the start date too

so orders must be 6 and returns must be 2

THIS IS WHY ITS SO HARD BUT THANK YOU SO MUCH FOR TRYING TO HELP, I APPRECIATE IT!

@MohamedGadAllah

THANK SO MUCH FOR TAKING THE TIME TO HELP ME.

PLEASE SEE MY RESPONSE BELOW TO EVERYONE.

THE RESULTS IS; orders = 6 and returns = 2 because results should not include 2 returns and 2 orders since all orders must occur after the start date of 10/1/2023 AND for returns, the returns must be for a serial number order that occurred after the start date too

@AliGW

Thank you for your response, I dont think I communicated the problem enough for your advice im sure you would of solved it perfectly if i did better in my post. thank you again, you're awesome!!

Please update your forum profile with your Excel version - this is very important. Find out what it is, please.

Does this do what you want?

=D6-SUMPRODUCT((Data!D:D="Return")*(Data!J:J=A6)*IFERROR((Data!V:V+0>=C6+0),1),Data!G:G)

Please test it thoroughly.

Hi jrich16
Please correct me if I am wrong, you only want to count the return that its order serial number happened after the start date.
I've used the data you provided in 1st post and resorted according to the serial number.
I've used the following sample from your data

Excel 2016 (Windows) 64 bit

	A	B	C	D	E	F	G	H
1	Serial number	Type	Delivery quantity	Customer #	Created on
2	0	Order	1	674700	10/1/2022
3	0	Return	1	674700	10/1/2023	1	OUT	because order date is before start date
4	1	Order	1	674700	10/1/2022
5	1	Return	1	674700	10/1/2023	1	OUT	because order date is before start date
6	2	Order	1	674700	10/1/2023
7	2	Return	1	674700	10/1/2023	1	IN	because order date is after start date
8	3	Order	1	674700	10/1/2023
9	3	Return	1	674700	10/1/2023	1	IN	because order date is after start date
10	4	Order	1	674700	10/1/2023
11	5	Order	1	674700	10/1/2023
12	6	Order	1	674700	10/1/2023
13	7	Order	1	674700	10/1/2023

Sheet: Sheet1

So did I understand it correctly and you want a formula that count what I've said?

YES! Returns will only be 2 if the formula is correct, orders will be 6

Did you try my suggestion in post #13?

Provide a workbook showing the issue, please - NOT a screenshot.

Thanks. I'm not following the logic, sorry. I'll bow out at this point.

Please don't, if you see the Excel that ends with "but 2 new rows added" it has 2 new rows of data added, your formula should provide the answer "4" since the two new rows are returns but shows "0"

In the original excel the return answer of 2 is correct and perfect, but when I add new data to the list, the formula no longer provides the right answer.

So the two excels attached are identical except "but 2 new rows added" has 15 rows of data and the original excel has only 13 rows of data where the formula you provided works.

I can't help - sorry. As I said, I don't understand the logic. It's not enough just knowing what the answer should be.

Others will have ideas - be patient.

VBA

Please Login or Register  to view this content.

"Data" is sorted and results are on sheet "Output", RUN button for macro

thank you jon but cant be VBA , i left all the dates the same as the return day can be the same day its ordered or a day after, it rly depends.

Sorry for the late reply.
Please check the attached document.

Formula:

Please Login or Register  to view this content.

Please load your full data to the file adjust the formula as needed and test many scenarios.
If there is any extra requirement, just tell me.

This is another much easier one using the LET function as it is much faster.

Formula:

Please Login or Register  to view this content.

Continuesd here:

https://www.excelforum.com/excel-for...ml#post5929443

There must be NO FURTHER ITERATIONS of this query.