sumifs or sumproduct? (or none?)

samantavelho

New Member
Joined
Jan 16, 2014
Messages
6
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 1id 2id2 strkBR strkg+hstrk behaviorf+i if c=c & d <>d
11878762214971010
11009752525761011
15001122624302020
11009752525761011
11009752275111011
11009752525761011
11009752525761011
11009752525761011
11009752525761011
11009752525761011
11009752275111011
11806172237532020
9113302349662020
101126173972020

<tbody>
</tbody>

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

Warm Regards,
 

Some videos you may like

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.

Son

Active Member
Joined
Mar 19, 2010
Messages
281
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 1id 2id2 strkBR strkg+hstrk behaviorf+i if c=c & d <>d
118787622149710100
110097525257610114
150011226243020200
110097525257610114
1100975227511101110
110097525257610112
110097525257610112
110097525257610112
110097525257610112
110097525257610112
110097522751110110
118061722375320200
91133023496620200
1011261739720200

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

FDibbins

Well-known Member
Joined
Feb 16, 2013
Messages
6,723
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

New Member
Joined
Jan 16, 2014
Messages
6
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

Active Member
Joined
Mar 19, 2010
Messages
281

ADVERTISEMENT

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 said:
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

New Member
Joined
Jan 16, 2014
Messages
6
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

Well-known Member
Joined
Oct 10, 2011
Messages
4,719
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Try:
Assumes your data starts in row 2.
Enter in G2 and copy down.
Code:
[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

New Member
Joined
Jan 16, 2014
Messages
6
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

Well-known Member
Joined
Oct 10, 2011
Messages
4,719
Office Version
  1. 365
Platform
  1. Windows
SV
Seems to work when I try it. Might want to check the cell references as I did lock some cells.
Excel Workbook
ABCDEFG
1id 1id 2id2 strkBR strkg+hstrk behaviorf+i if c=c & dd
2118787622149710101
3110097525257610113
4150011226243020202
5110097525257610113
6110097522751110118
7110097525257610113
8110097525257610113
9110097525257610113
10110097525257610113
11110097525257610113
12110097522751110118
13118061722375320202
1491133023496620202
151011261739720202
Sheet
 

samantavelho

New Member
Joined
Jan 16, 2014
Messages
6
Hi again AhoyNC,

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

:biggrin:

you have no idea how helpful this is!

I'm so very thankful!!!

kind regards,

SV
 

Watch MrExcel Video

Forum statistics

Threads
1,122,822
Messages
5,598,304
Members
414,224
Latest member
Crazy_FC

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top