Complex VBA Sumif

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Jeff - 16 May 2008 22:04 GMT

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

Reply to this Message

Brad - 16 May 2008 22:19 GMT

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.....

Signature

Wag more, bark less

> I urgently need to create a new table based on the model below.
> It is possible?
[quoted text clipped - 22 lines]
> Pool C Owner A 1
> Pool D Owner A 1

Reply to this Message

Jeff - 16 May 2008 22:38 GMT

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.

> No need for VBA
> =SUMPRODUCT(--($B$3:$B$12=B14),--($D$3:$D$12=C14),$C$3:$C$12)
[quoted text clipped - 35 lines]
> > Pool C Owner A 1
> > Pool D Owner A 1

Reply to this Message

Brad - 17 May 2008 03:11 GMT

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

Signature

Wag more, bark less

> Thank you.
> What happens if I don't the owners therefore I can't populate C14.
[quoted text clipped - 40 lines]
> > > Pool C Owner A 1
> > > Pool D Owner A 1

Reply to this Message

Lars-Åke Aspelin - 17 May 2008 17:36 GMT

>I urgently need to create a new table based on the model below.
>It is possible?
[quoted text clipped - 24 lines]
>
>

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

Reply to this Message