Sumifs column time = 00:00

[woon8888]

Hi experts, please help me review the below

=sumifs(A:A, B:B, time(0,0,0))

column B all has date with time but i only wish to sum those with 00:00

 

[Joe4]

Try this:

=SUMPRODUCT(--(B:B=INT(B:B)),A:A)

The reason that it works is because date/time is stored as numbers as Excel, specifically the number of days since 1/0/1900.
So time is just a fractional component of one day. So values with no time have no fractional component.

 

[Joe4]

Note if it is possible to have values in column A, and blanks in column B, and you do NOT want to include those rows, then you would just amend my above formula to:

=SUMPRODUCT(--(B:B=INT(B:B)),--(B:B>0),A:A)

 

[woon8888]

Hi Joe4,

Thank you for helping but apparent the sumproduct doesn't work. below is a table to show how the excel sheet is and i need to sum up col B base on col A's time being 00:00.

if i
1. sumif COL B, COL A, 00:00 i should get 365
2. sumif COL B, COL A, 11:00 i should get 100
3. sumif COL B, COL A, 15:00 i should get 123

Date & Time (A COL)	Quantity (B COL)
11/11:00	100
11/12:00	150
11/15:00	123
12/00:00	300
12/01:01	400
13/00:00	65

 

[6StringJazzer]

Your sample data in post #4 is organized the opposite of what you said in your first post.

column B all has date with time

Also I found that this formula does not work if text headings are present, so I would constrain the references to the used rows rather than the entire column.

$scratch.xlsm
	A	B	C	D
1	11/11:00	100		365
2	11/12:00	150
3	11/15:00	123
4	12/00:00	300
5	12/01:01	400
6	13/00:00	65
Sheet6

Cell Formulas
Range	Formula
D1	D1	=SUMPRODUCT(--(A:A=INT(A:A)),--(A:A>0),B:B)

 

[Joe4]

I would also questions those entries are valid time/date entries at all.
It looks like they could be text entries, in which case number/date formulas will not work on them.

That is why it is so important to give us an accurate description of your data!

 

[6StringJazzer]

Very important. I assumed they were date/time formatted as d/hh:mm (which is very eccentric) but if it's text, we start all over again.

 

[Dave Patton]

T202310a.xlsm
	A	B	C	D	E
1	Date & Time (A COL)	Quantity (B COL)
2	11/11:00	100		365	365
3	11/12:00	150
4	11/15:00	123
5	12/00:00	300
6	12/01:01	400
7	13/00:00	65
2h

Cell Formulas
Range	Formula
D2	D2	=SUMPRODUCT(--(MID(A2:A7,FIND("/",A2)+1,5)="00:00"),B2:B7)
E2	E2	=SUM((MID(A2:A7,FIND("/",A2)+1,5)="00:00")*B2:B7)

 

[woon8888]

Hi experts, those entries are time and date just formatted to dd/hh:mm format hence 12/00:00hrs

 

[Joe4]

Hi experts, those entries are time and date just formatted to dd/hh:mm format hence 12/00:00hrs

So what still appears to be the issue?

My original formula, adjusted for you switch of column A & B, for the data sample above:

=SUMPRODUCT(--(A2:A7=INT(A2:A7)),B2:B7)

If something still does not work right, please post a sample of your data, formula, and the output you are getting (and the output you are expecting).