I have a column that lists names and a column that list dates with time (i.e. 10/17/2022 1:23:00 PM that displays in the cell as 10/17/2022 13:23).

I am looking to sum a column based upon two factors, the name and date but how do I simplify the second factor to be just the date instead of the date and time?

Currently, in column "G" I have a formula of =IFERROR(LEFT($B2,FIND("h",$B2)-1),0)*60 IF(ISNUMBER(SEARCH("m",$B2)),TRIM(SUBSTITUTE(IFERROR(RIGHT($B2,LEN($B2)-FIND("h",$B2)),$B2),"m","")),0) so that I get a number in column G.

I want to sum any of the cells from column G if column A has the parameter of "Matthew" and column C has the parameter of 10/17/2022"

I would then end up having the parameters be entered from a separate location so that I could enter the varying name and date parameters to have the results calculated from the source data.

CodePudding user response：

Use SUMPRODUCT()

=SUMPRODUCT(G2:G500*(A2:A500="Matthew")*(INT(C2:C500)=DATE(2022,10,17))

or use SUMIFS with bookends:

=SUMIFS(G2:G500,A2:A500,"Matthew",C2:C500,">="&DATE(2022,10,17),C2:C500,"<"&DATE(2022,10,17) 1)

CodePudding user response：

As @ScottCraner pointed out, in order to use:

SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)

criteria_rangeN must be a range, so we cannot use INT(B2:B9) to remove the time portion of the data&time data as a criteria range. Excel stores date & time information as a decimal number, where the integer part is the date information.

Updated Solution

A way to overcome this limitation and being able to use INT to remove the time part would be to use FILTER(array,include,[if_empty]) because include is a Boolean array (not a range), therefore in cell F2:

=SUM(FILTER(C2:C9, (INT(B2:B9)=E3) * (A2:A9=E2)))

and here is the output:

where E3 is a date (not date & time), if that is not the case then use in the formula INT(E3) instead.