software

Hi all,

Many thanks if you can help me with this.

I have a table of 5 columns and 330 rows of data, in column A, each cell has
one of the numbers from 1 to 8. So for example 4 could be repeated around 40
times.

What I would like to do is add up all the numbers in columns B, C, D amp; E
dependant on which one of the 8 numbers is in column A. So I would get a
total for all the 1’s, a total for all the 2’s etc at the bottom of each of
the columns

Hope this makes sense and thank youAndyquot;Andy the yetiquot; gt; wrote in message
...
gt; Hi all,
gt;
gt; Many thanks if you can help me with this.
gt;
gt; I have a table of 5 columns and 330 rows of data, in column A, each cell
gt; has
gt; one of the numbers from 1 to 8. So for example 4 could be repeated around
gt; 40
gt; times.
gt;
gt; What I would like to do is add up all the numbers in columns B, C, D amp; E
gt; dependant on which one of the 8 numbers is in column A. So I would get a
gt; total for all the 1's, a total for all the 2's etc at the bottom of each
gt; of
gt; the columns
gt;
gt; Hope this makes sense and thank you
gt;
gt;
gt; Andy

Use SUMPRODUCT to test multiple criteria (SUMIF can use only one criterion).

See this:

www.xldynamic.com/source/xld.SUMPRODUCT.html

Ian
Try this:

A335: 1
A336: 2
A337: 3
etc

B335: =SUMPRODUCT(--($A$2:$A$330=A335)*($B$2:$E$330))
Copy that formula down as far as needed.Does that help?

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

XL2002, WinXP-Proquot;Andy the yetiquot; wrote:

gt; Hi all,
gt;
gt; Many thanks if you can help me with this.
gt;
gt; I have a table of 5 columns and 330 rows of data, in column A, each cell has
gt; one of the numbers from 1 to 8. So for example 4 could be repeated around 40
gt; times.
gt;
gt; What I would like to do is add up all the numbers in columns B, C, D amp; E
gt; dependant on which one of the 8 numbers is in column A. So I would get a
gt; total for all the 1’s, a total for all the 2’s etc at the bottom of each of
gt; the columns
gt;
gt; Hope this makes sense and thank you
gt;
gt;
gt; Andy
gt;

Thank you very much, those options are pefect.

Have a great Xmas all !!

A.

quot;ikrquot; wrote:

gt; quot;Andy the yetiquot; gt; wrote in message
gt; ...
gt; gt; Hi all,
gt; gt;
gt; gt; Many thanks if you can help me with this.
gt; gt;
gt; gt; I have a table of 5 columns and 330 rows of data, in column A, each cell
gt; gt; has
gt; gt; one of the numbers from 1 to 8. So for example 4 could be repeated around
gt; gt; 40
gt; gt; times.
gt; gt;
gt; gt; What I would like to do is add up all the numbers in columns B, C, D amp; E
gt; gt; dependant on which one of the 8 numbers is in column A. So I would get a
gt; gt; total for all the 1's, a total for all the 2's etc at the bottom of each
gt; gt; of
gt; gt; the columns
gt; gt;
gt; gt; Hope this makes sense and thank you
gt; gt;
gt; gt;
gt; gt; Andy
gt;
gt; Use SUMPRODUCT to test multiple criteria (SUMIF can use only one criterion).
gt;
gt; See this:
gt;
gt; www.xldynamic.com/source/xld.SUMPRODUCT.html
gt;
gt; Ian
gt;
gt;
gt;


SUMIF and OR would also work for multiple conditions.Try at the bottom of B assuming that is the column to sum if A has a 1
or a 2 in it.{=SUM(IF(OR($A$22:$A$27=1,A22:A27=2),$B$22:$B$27,0 ))}

Change the conditions for each column to sum as needed.

Commit with Ctrl-Shift-Enter as this is an array formula.

Cheers,

Steve--
SteveG
------------------------------------------------------------------------
SteveG's Profile: www.excelforum.com/member.php...foamp;userid=7571
View this thread: www.excelforum.com/showthread...hreadid=495500