Complex VBA Sumif
 

I urgently need to create a new table based on the model below.
It is possible?

Input Table

Column A Column B Column C
Pool A 2 Owner A
Pool A 1 Owner A
Pool A 4 Owner B
Pool A 5 Owner C
Pool B 10 Owner Z
Pool B 12 Owner X
Pool B 3 Owner X
Pool C 1 Owner A
Pool D 1 Owner A
Pool A 2 Owner C

Output Table

Pool A Owner A 3
Pool A Owner B 4
Pool A Owner C 7
Pool B Owner Z 10
Pool B Owner X 15
Pool C Owner A 1
Pool D Owner A 1

Complex VBA Sumif
 

No need for VBA
=SUMPRODUCT(--($B$3:$B$12=B14),--($D$3:$D$12=C14),$C$3:$C$12)

Where B3:B12 = the first column of pools
Where D3:D12 = the first column of owners
Where C3:C12 = the first column of numbers

B14 - is the first pool A of output
C14 - is the first owner of output
D14 has the formula above.

Copy the formula down.....

--
Wag more, bark less


"Jeff" wrote:

I urgently need to create a new table based on the model below.
It is possible?

Input Table

Column A Column B Column C
Pool A 2 Owner A
Pool A 1 Owner A
Pool A 4 Owner B
Pool A 5 Owner C
Pool B 10 Owner Z
Pool B 12 Owner X
Pool B 3 Owner X
Pool C 1 Owner A
Pool D 1 Owner A
Pool A 2 Owner C

Output Table

Pool A Owner A 3
Pool A Owner B 4
Pool A Owner C 7
Pool B Owner Z 10
Pool B Owner X 15
Pool C Owner A 1
Pool D Owner A 1

Complex VBA Sumif
 

Thank you.
What happens if I don't the owners therefore I can't populate C14.
The only thing I have is the input table. I need a formula to produce the
Output table.

"Brad" wrote:

No need for VBA
=SUMPRODUCT(--($B$3:$B$12=B14),--($D$3:$D$12=C14),$C$3:$C$12)

Where B3:B12 = the first column of pools
Where D3:D12 = the first column of owners
Where C3:C12 = the first column of numbers

B14 - is the first pool A of output
C14 - is the first owner of output
D14 has the formula above.

Copy the formula down.....

--
Wag more, bark less


"Jeff" wrote:

I urgently need to create a new table based on the model below.
It is possible?

Input Table

Column A Column B Column C
Pool A 2 Owner A
Pool A 1 Owner A
Pool A 4 Owner B
Pool A 5 Owner C
Pool B 10 Owner Z
Pool B 12 Owner X
Pool B 3 Owner X
Pool C 1 Owner A
Pool D 1 Owner A
Pool A 2 Owner C

Output Table

Pool A Owner A 3
Pool A Owner B 4
Pool A Owner C 7
Pool B Owner Z 10
Pool B Owner X 15
Pool C Owner A 1
Pool D Owner A 1

Complex VBA Sumif
 

You might find this link useful
Look at Debra Dalgleish's site for Advanced filter with unique records.
http://contextures.com/xladvfilter01.html#FilterUR

--
Wag more, bark less


"Jeff" wrote:

Thank you.
What happens if I don't the owners therefore I can't populate C14.
The only thing I have is the input table. I need a formula to produce the
Output table.

"Brad" wrote:

No need for VBA
=SUMPRODUCT(--($B$3:$B$12=B14),--($D$3:$D$12=C14),$C$3:$C$12)

Where B3:B12 = the first column of pools
Where D3:D12 = the first column of owners
Where C3:C12 = the first column of numbers

B14 - is the first pool A of output
C14 - is the first owner of output
D14 has the formula above.

Copy the formula down.....

--
Wag more, bark less


"Jeff" wrote:

I urgently need to create a new table based on the model below.
It is possible?

Input Table

Column A Column B Column C
Pool A 2 Owner A
Pool A 1 Owner A
Pool A 4 Owner B
Pool A 5 Owner C
Pool B 10 Owner Z
Pool B 12 Owner X
Pool B 3 Owner X
Pool C 1 Owner A
Pool D 1 Owner A
Pool A 2 Owner C

Output Table

Pool A Owner A 3
Pool A Owner B 4
Pool A Owner C 7
Pool B Owner Z 10
Pool B Owner X 15
Pool C Owner A 1
Pool D Owner A 1

Complex VBA Sumif
 

On Fri, 16 May 2008 14:04:01 -0700, Jeff
wrote:

I urgently need to create a new table based on the model below.
It is possible?

Input Table

Column A Column B Column C
Pool A 2 Owner A
Pool A 1 Owner A
Pool A 4 Owner B
Pool A 5 Owner C
Pool B 10 Owner Z
Pool B 12 Owner X
Pool B 3 Owner X
Pool C 1 Owner A
Pool D 1 Owner A
Pool A 2 Owner C

Output Table

Pool A Owner A 3
Pool A Owner B 4
Pool A Owner C 7
Pool B Owner Z 10
Pool B Owner X 15
Pool C Owner A 1
Pool D Owner A 1



By introducing the two helper columns D, E, and having the result
table in columns F, G, and H you may try these formulas:

In D1:
=SUMPRODUCT(--(A$1:A$10=A1),--(C$1:C$10=C1),B$1:B$10)

In E1:
=IF(SUMPRODUCT(--(A$1:A1=A1),--(C$1:C1=C1))=1,ROW(),11)

In F1:
=INDEX(A$1:A$11,SMALL(E$1:E$10,ROW()))

In G1:
=INDEX(C$1:C$11,SMALL(E$1:E$10,ROW()))

In H1:
=INDEX(D$1:D$11,SMALL(E$1:E$10,ROW()))

Copy all formulas in columns D to H down to row 10
To avoid the zeroes in the output table, enter blanks in cells A11,
C11, and D11.
Hide the two helper columns if you don't want to see them

All 10 and 11 in these formulas represents the number of data rows
and the number of data rows plus one respectively.

By using array formulas you can probably avoid the helper columns.
Someone else maybe can show how.

Hope this helps / Lars-Åke