SUMIFS formula? Maybe?

[ConVermin]

This will likely be long winded, I apologize now. (some data removed and names changed for NDA reasons)
Basically I'm trying to sum these by the number in the middle, using the Plus/minus and Steak/Pastry/Apples. In the full dataset, the numbers in the middle repeat and are shared by pastry and steak. For example, A number can both be plus and minus while also being from Pastry, Steak, and Apple. The formula I had prior =SUMIF(A:B,A2,B:B), would add up the numbers correctly, but I still have the issue of having multiple sums for the same concate in use.

I tried to explain it the best I could. If I didn't make sense I'm so sorry, I will try to explain further if need be.

Concate in use	Value to be Added	SUM
Pastry;1002757132;Plus	-4
Steak;1002757126;Plus	-12
Steak;526749;Plus	-10
Steak;201032;Minus	2
Apples;207598;Plus	-6
Steak;224892;Minus	2
Steak;155460;Minus	4
Steak;155559;Plus	-4
Steak;713708;Plus	-2

 

[Joe4]

Welcome to the Board!

I am sorry, but I really do not understand your question (and the fact that no one else has replied seems to suggest others may not understand either).
Can you show us what the EXPECTED result of your example should look like, and walk us through a calculation or two, step-by-step?

 

[ConVermin]

Sure! Maybe this will help. I probably should have included this the first time around. In the below, Apple has both a minus and plus, with values that are needed to be summed together. In this example,
Apple;666609;Plus, would only occupy one row to make it easier to vlookup in-between sheets.

Concate in use	Value to be Added	SUM
Apple;666609;Plus	-5	-10
Apple;666609;Minus	10	20
Apple;666609;Minus	10	20
Apple;666609;Plus	-5	-10
Pastry;666609;Plus	-5	-25
Pastry;666609;Plus	-20	-25

 

[etaf]

The formula I had prior =SUMIF(A:B,A2,B:B), would add up the numbers correctly, but I still have the issue of having multiple sums for the same concate in use.

you have A:B

=SUMIF($A$2:$A$100,A2,$B$2:$B$100)

on your original example all the items in column A were different and so i was not sure what you needed to be SUMMED

Book1
	A	B	C	D	E
1	Concate in use	Value to be Added	SUM
2	Apple;666609;Plus	-5	-10		-10
3	Apple;666609;Minus	10	20		20
4	Apple;666609;Minus	10	20		20
5	Apple;666609;Plus	-5	-10		-10
6	Pastry;666609;Plus	-5	-25		-25
7	Pastry;666609;Plus	-20	-25		-25
Sheet1

Cell Formulas
Range	Formula
E2:E7	E2	=SUMIF($A$2:$A$7,A2,$B$2:$B$7)

 

[Peter_SSs]

Is this what you mean then?

22 09 22.xlsm
	A	B	C
1	Concate in use	Value to be Added	SUM
2	Apple;666609;Plus	-5	-10
3	Apple;666609;Minus	10	20
4	Apple;666609;Minus	10	20
5	Apple;666609;Plus	-5	-10
6	Pastry;666609;Plus	-5	-25
7	Pastry;666609;Plus	-20	-25
SUMIF

Cell Formulas
Range	Formula
C2:C7	C2	=SUMIF(A$2:A$7,A2,B$2:B$7)

 

[Joe4]

Are you saying that you only want the sum to show up in the first instanace of each column A entry, like this:

Concate in use	Value to be Added	SUM
Apple;666609;Plus	-5​	-10​
Apple;666609;Minus	10​	20​
Apple;666609;Minus	10​
Apple;666609;Plus	-5​
Pastry;666609;Plus	-5​	-25​
Pastry;666609;Plus	-20​

If so, then try this formula:

=IF(COUNTIF(A$2:A2,A2)=1,SUMIF(A:B,A2,B:B),"")

 

[ConVermin]

you have A:B

=SUMIF($A$2:$A$100,A2,$B$2:$B$100)

on your original example all the items in column A were different and so i was not sure what you needed to be SUMMED

Book1
	A	B	C	D	E
1	Concate in use	Value to be Added	SUM
2	Apple;666609;Plus	-5	-10		-10
3	Apple;666609;Minus	10	20		20
4	Apple;666609;Minus	10	20		20
5	Apple;666609;Plus	-5	-10		-10
6	Pastry;666609;Plus	-5	-25		-25
7	Pastry;666609;Plus	-20	-25		-25
Sheet1

Cell Formulas
Range	Formula
E2:E7	E2	=SUMIF($A$2:$A$7,A2,$B$2:$B$7)

Thank you for your reply! That's more of less what I was getting. I'm trying to further break it down. For example, there being only one -10 or one 20 instead of two. I'm just trying to make it easier to vlookup, because it seems to be erroring out on the Vlookup when I try to move it between sheets due to multiple values.

 

[etaf]

can you give the example of what results you actually want

there being only one -10 or one 20 instead of two.

as you showed content in every row

I'm just trying to make it easier to vlookup,

How does vlookup come into it

Vlookup when I try to move it between sheets due to multiple values.

Again , explanation of multiple sheets and examples perhaps

I dont feel we have the full details

 

[Peter_SSs]

because it seems to be erroring out on the Vlookup when I try to move it between sheets due to multiple values.

Having multiple rows the same should not cause VLOOKUP to error. I suspect something else is going wrong.

Edit: Seems like I am continually a minute or so slower than @etaf

 

[ConVermin]

Are you saying that you only want the sum to show up in the first instanace of each column A entry, like this:

Concate in use	Value to be Added	SUM
Apple;666609;Plus	-5​	-10​
Apple;666609;Minus	10​	20​
Apple;666609;Minus	10​
Apple;666609;Plus	-5​
Pastry;666609;Plus	-5​	-25​
Pastry;666609;Plus	-20​

If so, then try this formula:

=IF(COUNTIF(A$2:A2,A2)=1,SUMIF(A:B,A2,B:B),"")

That's it! Thank you!