SUMIF returning wrong values

redspanna

Well-known Member
Joined
Jul 27, 2005
Messages
1,602
Office Version
  1. 365
Platform
  1. Windows
Hi all
I'm simply trying to gain the total hours worked for a range (in column B) only if the value in column A is populated with a N

N05:30=SUMIF(A1:A4,"N",B1:B4)
05:25
N06:31
N07:08

So on this example the formula returns the correct value of 19:09.

However if I add another row to the range I get a wrong returned value

N05:30=SUMIF(A1:A5,"N",B1:B5)
05:25
N06:31
N07:08
N06:22

The value returned now is 01:31, when it should be 25:31
All time columns are formatted with H:MM, so not sure what's going wrong

Sometime the simple things annoy the heck out of me :) and any help appreciated
 

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
Using the [] it will display the total hours, without them it will display the total for a day, so in your case its subtracting 24 hrs
 
Upvote 0
Using the [] it will display the total hours, without them it will display the total for a day, so in your case its subtracting 24 hrs

got it - many thanks for explaining
 
Upvote 0
You're welcome & thanks for the feedback.
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,215
Members
448,554
Latest member
Gleisner2

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