I am looking for the formula that will total the amounts of column B based on
Column A Matching Cell A10.

Col A B
Row 1: Red 10
Row 2: Blue 23
Row 3: Red 17
Row 4: Green 19

Row10: Red _____ (This should be 27)

The below formula only gives the result of 10... how to I get the total of
all numbers whose match the word "Red"?

=INDEX($A$1:$B$7,SMALL(IF($A$1:$A$4=$A$10,ROW($A$1 :$A$4)),ROW(1:1)),2)

Thank you in advance,
Joe

Try this:

Using your example
B10: =SUMIF(A1:A4,A10,B1:B4)

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro


"Joe P." wrote:

I am looking for the formula that will total the amounts of column B based on
Column A Matching Cell A10.

Col A B
Row 1: Red 10
Row 2: Blue 23
Row 3: Red 17
Row 4: Green 19

Row10: Red _____ (This should be 27)

The below formula only gives the result of 10... how to I get the total of
all numbers whose match the word "Red"?

=INDEX($A$1:$B$7,SMALL(IF($A$1:$A$4=$A$10,ROW($A$1 :$A$4)),ROW(1:1)),2)

Thank you in advance,
Joe

That was too easy ;) Is there a way to calculate the average instead of the
sum?

Thank you very much,
Joe


"Ron Coderre" wrote:

Try this:

Using your example
B10: =SUMIF(A1:A4,A10,B1:B4)

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro


"Joe P." wrote:

I am looking for the formula that will total the amounts of column B based on
Column A Matching Cell A10.

Col A B
Row 1: Red 10
Row 2: Blue 23
Row 3: Red 17
Row 4: Green 19

Row10: Red _____ (This should be 27)

The below formula only gives the result of 10... how to I get the total of
all numbers whose match the word "Red"?

=INDEX($A$1:$B$7,SMALL(IF($A$1:$A$4=$A$10,ROW($A$1 :$A$4)),ROW(1:1)),2)

Thank you in advance,
Joe

Try this:
B10: =SUMIF(A1:A4,A10,B1:B4)/COUNTIF(A1:A4,A10)

Alternatively, you could use this:
B10: =AVERAGE(IF(A1:A4=A10,B1:B4))
Note: Commit that array formula by holding down the [Ctrl][Shift] keys and
press [Enter].


Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro


"Joe P." wrote:

That was too easy ;) Is there a way to calculate the average instead of the
sum?

Thank you very much,
Joe


"Ron Coderre" wrote:

Try this:

Using your example
B10: =SUMIF(A1:A4,A10,B1:B4)

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro


"Joe P." wrote:

I am looking for the formula that will total the amounts of column B based on
Column A Matching Cell A10.

Col A B
Row 1: Red 10
Row 2: Blue 23
Row 3: Red 17
Row 4: Green 19

Row10: Red _____ (This should be 27)

The below formula only gives the result of 10... how to I get the total of
all numbers whose match the word "Red"?

=INDEX($A$1:$B$7,SMALL(IF($A$1:$A$4=$A$10,ROW($A$1 :$A$4)),ROW(1:1)),2)

Thank you in advance,
Joe