sumif

R

RFarrington

New Member
Joined
Sep 8, 2009
Messages
16
Hi, have a question regarding a sumif and countif functions. i have a spreadsheet with mulitple columns. Below are the 2 columns of data i'm working with. My column A is Date data, my column I is Total Minutes.
Ulimately, i need to sum the total minutes for monday thru friday, NOT including saturday and sunday. I also need to count in my report how many days monday - friday in the report. I've written the following statements but have to believe there's a better way.

=SUMIF(d,"=MON*",tm), i have this on my spreadsheet 5 times for each day of the week, and then sum total minutes.

=COUNTIF(d,"=mon*"), same, i have this 5 times and then sum.

The D in the formula is Date, TM in formula is total minutes.

Any help would be appreciated.


<TABLE style="WIDTH: 63pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=84><COLGROUP><COL style="WIDTH: 63pt; mso-width-source: userset; mso-width-alt: 3072" width=84><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 63pt; HEIGHT: 25.5pt; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: black 0.5pt solid" class=xl65 height=34 rowSpan=2 width=84>Date</TD></TR><TR style="HEIGHT: 12.75pt" height=17></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 63pt; HEIGHT: 12.75pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl67 height=17 width=84>WED 120110</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 63pt; HEIGHT: 12.75pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl67 height=17 width=84>THU 120210</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 63pt; HEIGHT: 12.75pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl67 height=17 width=84>FRI 120310</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 63pt; HEIGHT: 12.75pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl67 height=17 width=84>SAT 120410</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 63pt; HEIGHT: 12.75pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl67 height=17 width=84>SUN 120510</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 63pt; HEIGHT: 12.75pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl67 height=17 width=84>MON 120610</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 63pt; HEIGHT: 12.75pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl67 height=17 width=84>TUE 120710</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 63pt; HEIGHT: 12.75pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl67 height=17 width=84>WED 120810</TD></TR></TBODY></TABLE>

<TABLE style="WIDTH: 42pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=56><COLGROUP><COL style="WIDTH: 42pt; mso-width-source: userset; mso-width-alt: 2048" width=56><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 42pt; HEIGHT: 12.75pt; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: black 0.5pt solid" class=xl65 height=17 width=56>Total</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 42pt; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: black 0.5pt solid" class=xl66 height=17 width=56>Minutes</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 42pt; HEIGHT: 12.75pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl68 height=17 width=56>2265</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 42pt; HEIGHT: 12.75pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl68 height=17 width=56>1735</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 42pt; HEIGHT: 12.75pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl68 height=17 width=56>2527</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 42pt; HEIGHT: 12.75pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl69 height=17 width=56>1100</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 42pt; HEIGHT: 12.75pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl67 height=17 width=56>607</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 42pt; HEIGHT: 12.75pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl68 height=17 width=56>1780</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 42pt; HEIGHT: 12.75pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl68 height=17 width=56>1632</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 42pt; HEIGHT: 12.75pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl68 height=17 width=56>1360</TD></TR></TBODY></TABLE>
 

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number
Sort by date Sort by votes
Welcome to the board!

Try

=SUM(SUMIF(d,{"MON*","TUE*","WED*","THU*","FRI*"},tm))

=SUM(COUNTIF(d,{"MON*","TUE*","WED*","THU*","FRI*"}))
 
Upvote 0
RFarrington said:
Hi, have a question regarding a sumif and countif functions. i have a spreadsheet with mulitple columns. Below are the 2 columns of data i'm working with. My column A is Date data, my column I is Total Minutes.
Ulimately, i need to sum the total minutes for monday thru friday, NOT including saturday and sunday. I also need to count in my report how many days monday - friday in the report. I've written the following statements but have to believe there's a better way.

=SUMIF(d,"=MON*",tm), i have this on my spreadsheet 5 times for each day of the week, and then sum total minutes.

=COUNTIF(d,"=mon*"), same, i have this 5 times and then sum.

The D in the formula is Date, TM in formula is total minutes.

Any help would be appreciated.


<TABLE style="WIDTH: 63pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=84 border=0><COLGROUP><COL style="WIDTH: 63pt; mso-width-source: userset; mso-width-alt: 3072" width=84><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl65 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; WIDTH: 63pt; BORDER-BOTTOM: black 0.5pt solid; HEIGHT: 25.5pt; BACKGROUND-COLOR: transparent" width=84 height=34 rowSpan=2>Date</TD></TR><TR style="HEIGHT: 12.75pt" height=17></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl67 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black; BORDER-LEFT: black 0.5pt solid; WIDTH: 63pt; BORDER-BOTTOM: black 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=84 height=17>WED 120110</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl67 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black; BORDER-LEFT: black 0.5pt solid; WIDTH: 63pt; BORDER-BOTTOM: black 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=84 height=17>THU 120210</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl67 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black; BORDER-LEFT: black 0.5pt solid; WIDTH: 63pt; BORDER-BOTTOM: black 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=84 height=17>FRI 120310</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl67 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black; BORDER-LEFT: black 0.5pt solid; WIDTH: 63pt; BORDER-BOTTOM: black 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=84 height=17>SAT 120410</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl67 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black; BORDER-LEFT: black 0.5pt solid; WIDTH: 63pt; BORDER-BOTTOM: black 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=84 height=17>SUN 120510</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl67 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black; BORDER-LEFT: black 0.5pt solid; WIDTH: 63pt; BORDER-BOTTOM: black 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=84 height=17>MON 120610</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl67 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black; BORDER-LEFT: black 0.5pt solid; WIDTH: 63pt; BORDER-BOTTOM: black 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=84 height=17>TUE 120710</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl67 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black; BORDER-LEFT: black 0.5pt solid; WIDTH: 63pt; BORDER-BOTTOM: black 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=84 height=17>WED 120810</TD></TR></TBODY></TABLE>

<TABLE style="WIDTH: 42pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=56 border=0><COLGROUP><COL style="WIDTH: 42pt; mso-width-source: userset; mso-width-alt: 2048" width=56><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl65 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; WIDTH: 42pt; BORDER-BOTTOM: #f0f0f0; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=56 height=17>Total</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl66 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: #f0f0f0; BORDER-LEFT: black 0.5pt solid; WIDTH: 42pt; BORDER-BOTTOM: black 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=56 height=17>Minutes</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl68 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black; BORDER-LEFT: black 0.5pt solid; WIDTH: 42pt; BORDER-BOTTOM: black 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=56 height=17>2265</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl68 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black; BORDER-LEFT: black 0.5pt solid; WIDTH: 42pt; BORDER-BOTTOM: black 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=56 height=17>1735</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl68 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black; BORDER-LEFT: black 0.5pt solid; WIDTH: 42pt; BORDER-BOTTOM: black 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=56 height=17>2527</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl69 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black; BORDER-LEFT: black 0.5pt solid; WIDTH: 42pt; BORDER-BOTTOM: black 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=56 height=17>1100</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl67 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black; BORDER-LEFT: black 0.5pt solid; WIDTH: 42pt; BORDER-BOTTOM: black 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=56 height=17>607</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl68 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black; BORDER-LEFT: black 0.5pt solid; WIDTH: 42pt; BORDER-BOTTOM: black 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=56 height=17>1780</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl68 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black; BORDER-LEFT: black 0.5pt solid; WIDTH: 42pt; BORDER-BOTTOM: black 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=56 height=17>1632</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl68 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black; BORDER-LEFT: black 0.5pt solid; WIDTH: 42pt; BORDER-BOTTOM: black 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=56 height=17>1360</TD></TR></TBODY></TABLE>
Click to expand...
Assuming there are no empty cells within the named ranges.

=SUMIF(d,"<>S*",tm)

=COUNTIF(d,"<>S*")
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Solved
VBA Function to Paste Table into Outlook Email Resulting in HTML language in Email
Replies
8
Views
622
smozgur
smozgur
T
  • Question Question
Exporting Cell Range As PNG - Getting A White Border To The Bottom & Right - Why?
2
Replies
16
Views
180
jolivanes
jolivanes
V
  • Solved
How to put a space in this table in VBA?
Replies
1
Views
501
VBAEXCELNew
V
J
  • Question Question
Copy Picture - VBA
2
Replies
10
Views
204
Logit
Logit
B
  • Question Question
Excel Pivot Table to see the number of "Lost Calls"
Replies
0
Views
68
Bernie Galve
B

Forum statistics

Threads
1,224,564
Messages
6,179,547
Members
452,925
Latest member
duyvmex

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top