add a sum dependent upon criteria

[Ben]

I have a sheet which contains a unique pay number in one column e.g. Q. The pay number may appear in more than one row.

Without data sorting by this column so that identical matches are next to one another, does anyone know an IF formula that will say:

If (Q2 appears anywhere else in COL Q, add together A2 and the row where Q2 is matched, if no then A2.

I know if I data sort by Q first that I can just do if Q2 = Q3 etc but can i cut out the sort??

[Ian Mac]

On 2002-05-24 05:27, Ben wrote:
I have a sheet which contains a unique pay number in one column e.g. Q. The pay number may appear in more than one row.

Without data sorting by this column so that identical matches are next to one another, does anyone know an IF formula that will say:

If (Q2 appears anywhere else in COL Q, add together A2 and the row where Q2 is matched, if no then A2.

I know if I data sort by Q first that I can just do if Q2 = Q3 etc but can i cut out the sort??

I don't fully understand the qusetion, but try.

=SUMIF(Q1:Q100,"Q2",A1:A100)

is this what you mean?

[Ben]

Ian -
Sorry should elaborate a little.

a b c d
1 5 3 2 ab21
2 3 4 2 ab23
3 4 5 6 ab21

Assuming the above looks like a simplified version, I want to search through Column D for all occassions where identical matches occur, then if they do occur add together the rows. E.G - as d1 and d3 match, then add together A1 and A3.

Any clearer?

[Ian Mac]

On 2002-05-24 06:00, Ben wrote:
Ian -
Sorry should elaborate a little.

a b c d
1 5 3 2 ab21
2 3 4 2 ab23
3 4 5 6 ab21

Assuming the above looks like a simplified version, I want to search through Column D for all occassions where identical matches occur, then if they do occur add together the rows. E.G - as d1 and d3 match, then add together A1 and A3.

Any clearer?

adjusting what i've put:

=SUMIF(D1:D100,D21,A1:A100)

will do what you describe.

What would be the expected result in Column from the following:

Microsoft Excel - Book3 ___Running: xl97 : OS = Windows (32-bit) NT 4.00
(F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp
D10 =
	A	B	C	D	E
1	1	10	19	D21
2	2	11	20	D21
3	3	12	21	D22
4	4	13	22	D23
5	5	14	23	D24
6	6	15	24	D21
7	7	16	25	D23
8	8	17	26	D24
9	9	18	27	D21
Sheet1

To see the formula in the cells just click on the cells hyperlink

The above image was automatically generated by [HtmlMaker V1.27]
If you want FREE SOFT, click here and Colo will email the file to you
This code was graciously allowed to be modified: by Ivan F Moala All credit to Colo


or is this what you need:

Microsoft Excel - Book3 ___Running: xl97 : OS = Windows (32-bit) NT 4.00
(F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp
F21 =
	A	B	C	D	E
1	1	10	19	D21	:alert('=SUMPRODUCT(($D$1:D1=D1)*1,($A$1:A1)+($B$1:B1)+($C$1:C1))')>30
2	2	11	20	D21	:alert('=SUMPRODUCT(($D$1:D2=D2)*1,($A$1:A2)+($B$1:B2)+($C$1:C2))')>63
3	3	12	21	D22	:alert('=SUMPRODUCT(($D$1:D3=D3)*1,($A$1:A3)+($B$1:B3)+($C$1:C3))')>36
4	4	13	22	D23	:alert('=SUMPRODUCT(($D$1:D4=D4)*1,($A$1:A4)+($B$1:B4)+($C$1:C4))')>39
5	5	14	23	D24	:alert('=SUMPRODUCT(($D$1:D5=D5)*1,($A$1:A5)+($B$1:B5)+($C$1:C5))')>42
6	6	15	24	D21	:alert('=SUMPRODUCT(($D$1:D6=D6)*1,($A$1:A6)+($B$1:B6)+($C$1:C6))')>108
7	7	16	25	D23	:alert('=SUMPRODUCT(($D$1:D7=D7)*1,($A$1:A7)+($B$1:B7)+($C$1:C7))')>87
8	8	17	26	D24	:alert('=SUMPRODUCT(($D$1:D8=D8)*1,($A$1:A8)+($B$1:B8)+($C$1:C8))')>93
9	9	18	27	D21	:alert('=SUMPRODUCT(($D$1:D9=D9)*1,($A$1:A9)+($B$1:B9)+($C$1:C9))')>162
Sheet1

To see the formula in the cells just click on the cells hyperlink

The above image was automatically generated by [HtmlMaker V1.27]
If you want FREE SOFT, click here and Colo will email the file to you
This code was graciously allowed to be modified: by Ivan F Moala All credit to Colo


Any good?

[Ben]

Ian
Assuming your first example looks through column d for all references to "D21" (which match) then I would want all column A rows (where column D = D21) to be summed

It gets a little more complex also because I want other calculations than SUM.

Essentially this may be what I have.

A B C D E
Row
1 3 32.1 2 50 a1
2 5 35 3 48.5 a3
3 6 36 1 40 a1


Assuming thats my data. I want Column F to look through col E and If E1 = E2 then (a2+a3)


Column G may be If E1 = E2 ((A2 * B2) + (c2 * D2))

And So on.

My problem is, I think I need the IF function first to match any entries in colum E, then apply a mathematical equation.

Sorry to be a pest!

[Ian Mac]

On 2002-05-24 06:39, Ben wrote:
Ian
Assuming your first example looks through column d for all references to "D21" (which match) then I would want all column A rows (where column D = D21) to be summed

It gets a little more complex also because I want other calculations than SUM.

Essentially this may be what I have.

A B C D E
Row
1 3 32.1 2 50 a1
2 5 35 3 48.5 a3
3 6 36 1 40 a1


Assuming thats my data. I want Column F to look through col E and If E1 = E2 then (a2+a3)


Column G may be If E1 = E2 ((A2 * B2) + (c2 * D2))

And So on.

My problem is, I think I need the IF function first to match any entries in colum E, then apply a mathematical equation.

Sorry to be a pest!

What would the expected results from MY first example be in Column E for EACH for the cells.

If you could tell me what number you'de expect.

Like:

E1 = 3
E2 = 0
etc.

It does sound like you might need to use IF()'s but I'd like to try and understand fully before commiting an answer.

[Ben]

Ian,
Sorry it's been a long day.

Assuming summation of all D21 entries in your 1st eg then I would want total to be 18. (Sum of all Col a Rows where entry in D = D21)

E.g. E1 = 18

[Ian Mac]

On 2002-05-24 07:15, Ben wrote:
Ian,
Sorry it's been a long day.

Assuming summation of all D21 entries in your 1st eg then I would want total to be 18. (Sum of all Col a Rows where entry in D = D21)

E.g. E1 = 18

=SUMIF(D:D,D1,A:A)

the first thing I suggested (more or less)

Do me a favour, please look at the help files for SUMIF() before returning to tell me that it doesn't work.
_________________
Share the wealth!!
Ian Mac

[ This Message was edited by: Ian Mac on 2002-05-24 08:27 ]

[Ben]

Ian
sorry - first time i've used the site to request help.

[Ian Mac]

On 2002-05-24 09:50, Ben wrote:
Ian
sorry - first time i've used the site to request help.

Ben,

No problem, as you said, it's been a long day.

Sorry if I was a bit sharp. Managers

Did the SUMIF() work for you?