software

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 quot;Redquot;?

=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,
JoeTry this:

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

Does that help?

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

XL2002, WinXP-Proquot;Joe P.quot; wrote:

gt; I am looking for the formula that will total the amounts of column B based on
gt; Column A Matching Cell A10.
gt;
gt; Col A B
gt; Row 1: Red 10
gt; Row 2: Blue 23
gt; Row 3: Red 17
gt; Row 4: Green 19
gt;
gt; Row10: Red _____ (This should be 27)
gt;
gt; The below formula only gives the result of 10... how to I get the total of
gt; all numbers whose match the word quot;Redquot;?
gt;
gt; =INDEX($A$1:$B$7,SMALL(IF($A$1:$A$4=$A$10,ROW($A$1 :$A$4)),ROW(1:1)),2)
gt;
gt; Thank you in advance,
gt; Joe
gt;

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

Thank you very much,
Joequot;Ron Coderrequot; wrote:

gt; Try this:
gt;
gt; Using your example
gt; B10: =SUMIF(A1:A4,A10,B1:B4)
gt;
gt; Does that help?
gt;
gt; ***********
gt; Regards,
gt; Ron
gt;
gt; XL2002, WinXP-Pro
gt;
gt;
gt; quot;Joe P.quot; wrote:
gt;
gt; gt; I am looking for the formula that will total the amounts of column B based on
gt; gt; Column A Matching Cell A10.
gt; gt;
gt; gt; Col A B
gt; gt; Row 1: Red 10
gt; gt; Row 2: Blue 23
gt; gt; Row 3: Red 17
gt; gt; Row 4: Green 19
gt; gt;
gt; gt; Row10: Red _____ (This should be 27)
gt; gt;
gt; gt; The below formula only gives the result of 10... how to I get the total of
gt; gt; all numbers whose match the word quot;Redquot;?
gt; gt;
gt; gt; =INDEX($A$1:$B$7,SMALL(IF($A$1:$A$4=$A$10,ROW($A$1 :$A$4)),ROW(1:1)),2)
gt; gt;
gt; gt; Thank you in advance,
gt; gt; Joe
gt; gt;

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-Proquot;Joe P.quot; wrote:

gt; That was too easy Is there a way to calculate the average instead of the
gt; sum?
gt;
gt; Thank you very much,
gt; Joe
gt;
gt;
gt; quot;Ron Coderrequot; wrote:
gt;
gt; gt; Try this:
gt; gt;
gt; gt; Using your example
gt; gt; B10: =SUMIF(A1:A4,A10,B1:B4)
gt; gt;
gt; gt; Does that help?
gt; gt;
gt; gt; ***********
gt; gt; Regards,
gt; gt; Ron
gt; gt;
gt; gt; XL2002, WinXP-Pro
gt; gt;
gt; gt;
gt; gt; quot;Joe P.quot; wrote:
gt; gt;
gt; gt; gt; I am looking for the formula that will total the amounts of column B based on
gt; gt; gt; Column A Matching Cell A10.
gt; gt; gt;
gt; gt; gt; Col A B
gt; gt; gt; Row 1: Red 10
gt; gt; gt; Row 2: Blue 23
gt; gt; gt; Row 3: Red 17
gt; gt; gt; Row 4: Green 19
gt; gt; gt;
gt; gt; gt; Row10: Red _____ (This should be 27)
gt; gt; gt;
gt; gt; gt; The below formula only gives the result of 10... how to I get the total of
gt; gt; gt; all numbers whose match the word quot;Redquot;?
gt; gt; gt;
gt; gt; gt; =INDEX($A$1:$B$7,SMALL(IF($A$1:$A$4=$A$10,ROW($A$1 :$A$4)),ROW(1:1)),2)
gt; gt; gt;
gt; gt; gt; Thank you in advance,
gt; gt; gt; Joe
gt; gt; gt;