Sumifs dates, but some have time also :-(

[MOB]

My data looks like this - I want to sumifs using the data, but it doesn't pick up the ones with time as they aren't integers.

I could of course change the source data but I'm trying to automate a process so would prefer a formula to deal with it - so in the example below sumifs should treat the first 2 as the same date;

Any help would be greatly appreciated!

07/08/2023 12:56

07/08/2023

23/09/2022 08:46

23/09/2022

 

[Peter_SSs]

What is the range shown in your post?
What is your current formula?
What exactly are you trying to sum?

 

[Joe4]

There are a few options, as shown here: SUMIF - dates, ignore times?

 

[MOB]

What is the range shown in your post?
What is your current formula?
What exactly are you trying to sum?

The range will be large, 40 columns and approx 250k rows

Current formula to be created - it will look at 2 criteria columns, one of them the date column, another a department code, and then sum £ value

The sum will be £ values

 

[MOB]

I re

There are a few options, as shown here: SUMIF - dates, ignore times?

I reckon this will work for me, thanks

=SUMIFS([SumRang], [daterange], ">=" Day, [daterange], < Day + 1)

 

[Peter_SSs]

How do you know ...

it doesn't pick up the ones with time

.. if you don't already have a formula?

The range will be large, 40 columns and approx 250k rows

I asked about the range shown in your post which clearly is just a single column.

What about something like this?

23 08 23.xlsm
	A	B	C	D	E	F
1	Date Time	Dept Code	Value		Date	7/08/2023
2	7/08/2023 12:56	a	3		Dept Code	a
3	7/08/2023	a	2		Sum	5
4	23/09/2022 8:46	a	1
5	23/09/2022	b	4
Replace SUMIF

Cell Formulas
Range	Formula
F3	F3	=SUM(FILTER(C2:C5,(INT(A2:A5)=F1)*(B2:B5=F2),0))

 

[MOB]

How do you know ...

.. if you don't already have a formula?


I asked about the range shown in your post which clearly is just a single column.

What about something like this?

23 08 23.xlsm
	A	B	C	D	E	F
1	Date Time	Dept Code	Value		Date	7/08/2023
2	7/08/2023 12:56	a	3		Dept Code	a
3	7/08/2023	a	2		Sum	5
4	23/09/2022 8:46	a	1
5	23/09/2022	b	4
Replace SUMIF

Cell Formulas
Range	Formula
F3	F3	=SUM(FILTER(C2:C5,(INT(A2:A5)=F1)*(B2:B5=F2),0))

Thanks I will try your suggestion

You raise valid points above, my initial post was based on what I am about to attempt and the issue I knew I was going to encounter, I have got round it by turning the data into integers before but want a slicker attempt this time!

 

[MOB]

How do you know ...

.. if you don't already have a formula?


I asked about the range shown in your post which clearly is just a single column.

What about something like this?

23 08 23.xlsm
	A	B	C	D	E	F
1	Date Time	Dept Code	Value		Date	7/08/2023
2	7/08/2023 12:56	a	3		Dept Code	a
3	7/08/2023	a	2		Sum	5
4	23/09/2022 8:46	a	1
5	23/09/2022	b	4
Replace SUMIF

Cell Formulas
Range	Formula
F3	F3	=SUM(FILTER(C2:C5,(INT(A2:A5)=F1)*(B2:B5=F2),0))

Works well, many thanks

 

[Peter_SSs]

You're welcome. Thanks for the confirmation.