Sumif

[Davidns]

How can i set a criterion to be equal to the day of a given month? In other words, the criteria range is filled with dates, and i need to sum those dates that have a particular day of the month.
Thanks!

 

[Yongle]

you may need to amend the formula if your regional settings are set to accept dates differently

To count the number of times 27 November 2018 appears in column A, use this
=COUNTIF(A:A,"=27/11/2018")

You mention SUMIF, which suggests requirement to sum values in another column for a single date

To sum the values in column B where the date in column A is 27 November 2018
=SUMIFS(B:B,A:A,"=27/11/2018")

To make the formula more flexible, use cells to hold the values for day (F2), month (G2) and year (H2)
=SUMIFS(B:B,A:A,"="&F2&"/"&G2&"/"&H2)

etc

 

[Yongle]

or use SUMPRODUCT

=SUMPRODUCT((DAY($A$2:$A$1000)=27)*(MONTH($A$2:$A$1000)=11)*(YEAR($A$2:$A$1000)=2018)*($B$2:$B$1000))

add flexibility (with day in F2, month in G2, year in H2)
=SUMPRODUCT((DAY($A$2:$A$1000)=$F$2)*(MONTH($A$2:$A$1000)=$G$2)*(YEAR($A$2:$A$1000)=$H$2)*($B$2:$B$1000))

 

[Davidns]

Thank you. Is there any way to set the criteria to match the specific day of the month (ie the 27th) without creating a new cell with days of the month? In other words, can you use a formula in the criteria cell incorporating the "day" formula? I didn't see a way, but maybe I am missing something.
Thanks

 

[Yongle]

can you use a formula in the criteria cell incorporating the "day" formula?

I must be misunderstanding something

Is that not what this does?
- it incorporates the DAY formula without using another cell for "day of the month"

=SUMPRODUCT((DAY($A$2:$A$1000)=27)*(MONTH($A$2:$A$1000)=11)*(YEAR($A$2:$A$1000)=2018)*($B$2:$B$1000))

 

[jtakw]

Hi,

I think may be OP means he just want to count the dates that are a particular day:

Book1
	A	B	C	D	E	F
1	11/27/2018		4	4	Day Criteria	27
2	1/27/2018
3	2/15/2018
4	6/27/2016
5	9/27/2015
Sheet335

Cell Formulas
Range	Formula
C1	=SUMPRODUCT(--(DAY(A1:A5)=27))
D1	=SUMPRODUCT(--(DAY(A1:A5)=F1))

 

[Yongle]

@jtakw - thanks

@Davidns
- is post#6 what you want?
- if not, please tabulate sample data and desired results to illustrate your requirements

thanks

 

[Davidns]

Thanks everyone. Looks like sumproduct is the way to go. I haven't used it before so will definitely learn more about it and start to use it.