SUM Range more than one column - SUMIF

[shemayisroel]

Hi,

How would I SUM all the results from results 1-5 if say ITEM in column A = C?

SUMIF wont work as it only sums the first column...

<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 10pt; FONT-FAMILY: Arial,Arial; BACKGROUND-COLOR: #ffffff" cellSpacing=0 cellPadding=0 border=1><COLGROUP><COL style="FONT-WEIGHT: bold; WIDTH: 30px"><COL style="WIDTH: 64px"><COL style="WIDTH: 69px"><COL style="WIDTH: 69px"><COL style="WIDTH: 69px"><COL style="WIDTH: 69px"><COL style="WIDTH: 69px"></COLGROUP><TBODY><TR style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center"><TD> </TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD><TD>E</TD><TD>F</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">1</TD><TD>ITEM</TD><TD style="FONT-WEIGHT: bold">RESULT 1</TD><TD style="FONT-WEIGHT: bold">RESULT 2</TD><TD style="FONT-WEIGHT: bold">RESULT 3</TD><TD style="FONT-WEIGHT: bold">RESULT 4</TD><TD style="FONT-WEIGHT: bold">RESULT 5</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">2</TD><TD>A</TD><TD style="TEXT-ALIGN: center">1</TD><TD style="TEXT-ALIGN: center">2</TD><TD style="TEXT-ALIGN: center">2</TD><TD style="TEXT-ALIGN: center">77</TD><TD style="TEXT-ALIGN: center">2</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">3</TD><TD>B</TD><TD style="TEXT-ALIGN: center">10</TD><TD style="TEXT-ALIGN: center">7</TD><TD style="TEXT-ALIGN: center">5</TD><TD style="TEXT-ALIGN: center">5</TD><TD style="TEXT-ALIGN: center">5</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">4</TD><TD>C</TD><TD style="TEXT-ALIGN: center">2</TD><TD style="TEXT-ALIGN: center">1</TD><TD style="TEXT-ALIGN: center">77</TD><TD style="TEXT-ALIGN: center">2</TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">5</TD><TD>A</TD><TD style="TEXT-ALIGN: center">10</TD><TD style="TEXT-ALIGN: center">10</TD><TD> </TD><TD style="TEXT-ALIGN: center">33</TD><TD style="TEXT-ALIGN: center">7</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">6</TD><TD>A</TD><TD style="TEXT-ALIGN: center">5</TD><TD> </TD><TD style="TEXT-ALIGN: center">2</TD><TD style="TEXT-ALIGN: center">33</TD><TD style="TEXT-ALIGN: center">5</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">7</TD><TD>B</TD><TD style="TEXT-ALIGN: center">1</TD><TD style="TEXT-ALIGN: center">33</TD><TD> </TD><TD style="TEXT-ALIGN: center">7</TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">8</TD><TD>C</TD><TD style="TEXT-ALIGN: center">7</TD><TD> </TD><TD style="TEXT-ALIGN: center">1</TD><TD> </TD><TD style="TEXT-ALIGN: center">2</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">9</TD><TD>C</TD><TD style="TEXT-ALIGN: center">5</TD><TD style="TEXT-ALIGN: center">5</TD><TD style="TEXT-ALIGN: center">5</TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">10</TD><TD>B</TD><TD style="TEXT-ALIGN: center">7</TD><TD style="TEXT-ALIGN: center">2</TD><TD style="TEXT-ALIGN: center">7</TD><TD style="TEXT-ALIGN: center">7</TD><TD style="TEXT-ALIGN: center">2</TD></TR></TBODY></TABLE>

 

[Domenic]

Try...

=SUM(IF(A2:A10="C",B2:F10))

...confirmed with CONTROL+SHIFT+ENTER.

 

[sanrv1f]

try

=SUMPRODUCT((A2:A10="A")*B2:F10)

or

=SUM(IF(A2:A10="A",B2:F10))

with CSE

 

[T. Valko]

Try this:

=SUMPRODUCT((A2:A10="A")*B2:F10)

 

[shemayisroel]

Try...

=SUM(IF(A2:A10="C",B2:F10))

...confirmed with CONTROL+SHIFT+ENTER.

Of course - thank you.

Excuse my brain freeze. I guess I was trying to tackle it with a sledge hammer when a simple tap with a hammer did the trick.

Thanks

And BTW, your website is GREAT. Really handy and easy to uderstand solutions. Well done.

 

[shemayisroel]

Thanks sanrv1f & #NAME?

My bad habit with SUMPRODUCT of using , at the end rather than *

Thanks for the reminder and solution

 

[Domenic]

Of course - thank you.

You're very welcome!

And BTW, your website is GREAT. Really handy and easy to uderstand solutions. Well done.

Thank you, shemayisroel! I really appreciate it!

 

[Acon]

Does anyone know how to sum multiple coloumns with multiple conditions? Should I use sumif or sumproduct function?

 

[Domenic]

Does anyone know how to sum multiple coloumns with multiple conditions? Should I use sumif or sumproduct function?

It really depends on your actual requirements. I'm on my way out, so I would suggest that you start a new thread and provide an example of the data, describe what you'd like to do, and provide the actual results based on your sample data.

 

[uckocak]

It really depends on your actual requirements. I'm on my way out, so I would suggest that you start a new thread and provide an example of the data, describe what you'd like to do, and provide the actual results based on your sample data.

I Tried that array sum if formula, however it keeps generating " excel running out of resources, formula cant be calculated" error message!
my pc has 16 GB ram and I7 processor, shouldn't be an issue to do a simple sum if I guess.

what am I doing wrong?