Formula for the sum of multiple collumns

[DustyH]

hELLO I'M LEARNING EXCEL AND NEEDING TO KEPP TRACK OF JUST OVERTIME HOURS. i HAVE COLLUMN A1 WITH 31.50 HOURS COLLUMN B1 WITH 43 HOURS AND COLLUMN C1 WITH 42.50 HOURS MY NEXT COLLUMN D1 IS SUPPOSED TO BE THE TOTAL OF ONLY EVERYTHING OVER 40 HOURS
WHICH SHOULD BE 5.50 CAN ANYONE PLEASSE HELP ME FIGURE OUT HOW TO GET THIS FORMULA. I TOOK AN ECXEL CLASS LAST YEAR AND GRADUATED ON THE BASICS--JUST CALLED MY TEACHER FOR HELP ON THIS AND SHE COULD NOT FIGURE IT OUT EITHER. THANKS

 

[Domenic]

Try...

=SUMPRODUCT(--(A1:C1>40),A1:C1-40)

or

=SUM(IF(A1:C1>40,A1:C1-40))

...confirmed with CONTROL+SHIFT+ENTER.

 

[DustyH]

THANKS FOR YOUR HELP!!! I HAVE BEEN TRYING TO FIGURE THIS OUT FOR A WEEK. i COULD NOT GET YOU SECOND SUGGESTION SUMIF TO WORK BUT YOUR FIRST OPTION WORKS GREAT!!!!

 

[bethg7]

Not sure if this is what you want. Hope it helps you get started.


Sheet1


<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Calibri,Arial; FONT-SIZE: 12pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 72px"><COL style="WIDTH: 72px"><COL style="WIDTH: 72px"><COL style="WIDTH: 72px"><COL style="WIDTH: 72px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD> </TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD><TD>E</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</TD><TD>Week 1</TD><TD>Week 2</TD><TD>Week 3</TD><TD> </TD><TD>Hours</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD style="TEXT-ALIGN: right">31.5</TD><TD style="TEXT-ALIGN: right">43</TD><TD style="TEXT-ALIGN: right">42.5</TD><TD>Total</TD><TD style="TEXT-ALIGN: right">117</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</TD><TD> </TD><TD style="TEXT-ALIGN: right">3</TD><TD style="TEXT-ALIGN: right">2.5</TD><TD>Overtime</TD><TD style="TEXT-ALIGN: right">5.5</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</TD><TD> </TD><TD> </TD><TD> </TD><TD>Regular</TD><TD style="TEXT-ALIGN: right">111.5</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">5</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR></TBODY></TABLE>


<TABLE style="BORDER-BOTTOM-STYLE: groove; BORDER-BOTTOM-COLOR: #00ff00; BORDER-RIGHT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-TOP-STYLE: groove; COLOR: #000000; BORDER-RIGHT-COLOR: #00ff00; FONT-SIZE: 10pt; BORDER-LEFT-STYLE: groove; BORDER-LEFT-COLOR: #00ff00"><TBODY><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-FAMILY: Arial; FONT-SIZE: 9pt" border=1 cellSpacing=0 cellPadding=2><TBODY><TR style="BACKGROUND-COLOR: #cacaca; FONT-SIZE: 10pt"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>E2</TD><TD>=SUM(A2:C2)</TD></TR><TR><TD>A3</TD><TD>=IF(A2<=40,"",A2-40)</TD></TR><TR><TD>B3</TD><TD>=IF(B2<=40,B2,B2-40)</TD></TR><TR><TD>C3</TD><TD>=IF(C2<=40,C2,C2-40)</TD></TR><TR><TD>E3</TD><TD>=SUM(A3:C3)</TD></TR><TR><TD>E4</TD><TD>=E2-E3</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>


Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4

Beth

 

[Domenic]

THANKS FOR YOUR HELP!!!

You're very welcome!

I COULD NOT GET YOU SECOND SUGGESTION SUMIF TO WORK...

The second one needs to be confirmed with CONTROL+SHIFT+ENTER, not just ENTER. If done correctly, Excel will automatically place curly brackets around the formula.

 

[kwedde01]

How do I count multiple columns? What forumla can I use?

For example:

A B C D E
1 1
1 1
1 1 1
1 1 1
1 1

AB = 3
ABC = 2


Thank you in advance

 

[Domenic]

For the number of times Column A equals 1, Column B equals 1, and Column C does not equal 1, try...

=SUMPRODUCT(--(A2:A6=1),--(B2:B6=1),--(C2:C6<>1))

For the number of times Column A equals 1, Column B equals 1, and Column C equals 1, try...

=SUMPRODUCT(--(A2:A6=1),--(B2:B6=1),--(C2:C6=1))

 

[kwedde01]

How do I count the following numbers listed as text?

1,2,3,4,5,11,13
1
2,3,4
1,10
1
1
1,12

The present formula I use goes something like this: SUM(LEN(A1-A10)-LEN(SUBSTITUTE(LOWER(A1-A10),"1","")))

The problem is that the count is including all numbers including the 10,11,12,13 etc.

I want to count all the instances of 1 occurring not all instances of 1 occurring in a number.

Can someone please help

 

[Domenic]

Try...

=SUMPRODUCT(LEN(","&A1:A10&",")-LEN(SUBSTITUTE(","&A1:A10&",",",1,","")))/3