sumifs or sumproduct? (or none?)

[samantavelho]

Hi all,

I've been trying to find an answer to my problem, before bothering you, and I've already spent a few hours today browsing the treads here in the forum, but did not succeed to find a suitable answer

The thing is:

I need to have in (what wuld be in this case the cell) G2, the sum of id2 strk (column C) with strk behavior (column F), but only if id1 (for instance cell A2 1187876) is repeated in column A range, and if id2 (cell B2) is different from the value of the row I'm calculating (in this case different from 221497).

I've tried =sumifs(A:A=A2,B:B"<>"&B2,C:F) but didn't work.


bellow you can find the information.

id 1	id 2	id2 strk	BR strk	g+h	strk behavior	f+i if c=c & d <>d
1187876	221497	1	0	1	0
1100975	252576	1	0	1	1
1500112	262430	2	0	2	0
1100975	252576	1	0	1	1
1100975	227511	1	0	1	1
1100975	252576	1	0	1	1
1100975	252576	1	0	1	1
1100975	252576	1	0	1	1
1100975	252576	1	0	1	1
1100975	252576	1	0	1	1
1100975	227511	1	0	1	1
1180617	223753	2	0	2	0
911330	234966	2	0	2	0
101126	17397	2	0	2	0

<tbody>
</tbody>

I would be very thankful if any of you could shed some light.

Warm Regards,

 

[Son]

I am not really sure what you need to do, and I can only suggest sumproduct, because sumifs do not work in my excel 2003 version.
So, if you need to get the results I've provided in the table, you will need to write on G2

=SUMPRODUCT(--(A2:A15=A2),--(B2:B15<>B2),C2:C15)+SUMPRODUCT(--(A2:A15=A2),--(B2:B15<>B2),F2:F15)

and copy down.

id 1	id 2	id2 strk	BR strk	g+h	strk behavior	f+i if c=c & d <>d
1187876	221497	1	0	1	0	0
1100975	252576	1	0	1	1	4
1500112	262430	2	0	2	0	0
1100975	252576	1	0	1	1	4
1100975	227511	1	0	1	1	10
1100975	252576	1	0	1	1	2
1100975	252576	1	0	1	1	2
1100975	252576	1	0	1	1	2
1100975	252576	1	0	1	1	2
1100975	252576	1	0	1	1	2
1100975	227511	1	0	1	1	0
1180617	223753	2	0	2	0	0
911330	234966	2	0	2	0	0
101126	17397	2	0	2	0	0

<colgroup><col width="64" span="7" style="width:48pt"> </colgroup><tbody>
</tbody>

 

[FDibbins]

Im not sure I totally understand your requirements, but based on what you described, I came up with this.
=SUMIFS($C$2:$C$15,$A$2:$A$15,COUNTIF($A$2:$A$15,A2)>1,$B$2:$B$15,"<>"&B2)
This gave me zero for all rows, but maybe you can tweak it for your needs?

your syntax for the sumifs() you showed above was incorrect, with SUMIF() the sum range goes at the end, but with sumifS() it goes in the beginning

 

[samantavelho]

Thanks Son and FDibbins for your quick feedback,

I'm feeling like the more I try to explain the more confused I get.

But if I would have to say in words what I need it would be:
focusing in the 3r row: I need to add to C3 the sum of column values in F ONLY IF the ID in column A (1100975) repeats, but if ID of column B is not the one in B3. that would mean that all the rows that have ID1 1100975, but ID2 IS NOT 252576 but 227511, the sum in G3 would be 3 (1 from cell C3 + 1 from F6 + 1 from F12).
But with ID1 1100975 and ID2 252576 (not 227511) the result would be 8.
For instance G5 = C5+G2+G4+G6+G7+G8+G9+G10 = 8

Does that make any sense now? (I doubt)

Regards,

SV

 

[Son]

252576 but [/COLOR]227511,

the sum in G3 would be 3 (1 from cell C3 + 1 from F6 + 1 from F12).


But with ID1 1100975 and ID2 252576 (not 227511) the result would be 8.
For instance G5 = C5+G2+G4+G6+G7+G8+G9+G10 = 8

****
Does that make any sense now? (I doubt)

Regards,

SV

227511 is in rows 6 and 12. Why do you add C3 in this? shouldn't you add c6 + f6 + c12 + f12 = 4?
252576 is in rows 3, 5, 7, 8, 9, 11. How come Gxx is in your formula? Shouldn't you add columns C and F?

you need to explain better what you want to do.

 

[samantavelho]

So sorry Son.

got messed up with the file I'm working with, it would be:
G3=C3+F6+F12

G6=C6+F3+F5+F7+F8+F9+F10+F11

because I need to find the common ID in A, but in column B I need the entries that match the ID in column A, but do not match themselves - g3 sums Fs different from b3 IDs (<>252576), and g6 the ones different from b6 IDs (<>227511) (but both have the same ID in column A 1100975).

Thanks for the time you're taking with this one.

SV

 

[AhoyNC]

Try:
Assumes your data starts in row 2.
Enter in G2 and copy down.

[TABLE]
<colgroup><col width="446"></colgroup><tbody>[TR]
   [TD="width: 446"]=C2+SUMIFS($F$2:$F$15,$A$2:$A$15,"="&A2,$B$2:$B$15,"<>"&B2)
[/TD]
 [/TR]
</tbody>[/TABLE]

 

[samantavelho]

Hi AhoyNC,

Thanks for the input!
I've just tried your formula, but it returns #VALUE (I've already checked the format of the cells and it's all "number").

I'm thinking about work with parallel sheets with vlookups and/or if functions to try my luck, still don't know exactly how I'll structure it.

Thanks a lot!

SV

 

[AhoyNC]

SV
Seems to work when I try it. Might want to check the cell references as I did lock some cells.

Excel Workbook
	A	B	C	D	E	F	G
1	id 1	id 2	id2 strk	BR strk	g+h	strk behavior	f+i if c=c & dd
2	1187876	221497	1	0	1	0	1
3	1100975	252576	1	0	1	1	3
4	1500112	262430	2	0	2	0	2
5	1100975	252576	1	0	1	1	3
6	1100975	227511	1	0	1	1	8
7	1100975	252576	1	0	1	1	3
8	1100975	252576	1	0	1	1	3
9	1100975	252576	1	0	1	1	3
10	1100975	252576	1	0	1	1	3
11	1100975	252576	1	0	1	1	3
12	1100975	227511	1	0	1	1	8
13	1180617	223753	2	0	2	0	2
14	911330	234966	2	0	2	0	2
15	101126	17397	2	0	2	0	2
Sheet

 

[samantavelho]

Hi again AhoyNC,

you were right, it was a question of adjusting the references!
you saved me a week of work!



you have no idea how helpful this is!

I'm so very thankful!!!

kind regards,

SV