Multiple Sum, based on criteria

[Guest]

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 & 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 you


Andy

 

[ikr]

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 & 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 you


Andy

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

See this:

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

Ian

 

[Guest]

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

 

[Guest]

Thank you very much, those options are pefect.

Have a great Xmas all !!

A.

 

[SteveG]

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