CODEWITHSUNDEEP
excelexcel-formula
Andrei Ion
Andrei Ion

Reputation: 1827

SUMIF when the range is a range of dates

Here's what I want to do.
I have the following table: enter image description here

And another one that looks like this: enter image description here

Here's what I want to do. A SUMIF .. the range is the A column in the first table (picture 1) ...but the problem is here... the criteria is that I want the day in that date to be equal with the number in the 2nd table... and then I want to sum from column D. I tried the formula but it didn't work. Could you help me with this situation? Thanks!

Upvotes: 0

Views: 2372

Answers (3)

MikeD
MikeD

Reputation: 8941

"the criteria is that I want the day in that date to be equal with the number in the 2nd table"

As a date consists of a day AND month, year, just to provide a day as parameter isn't enough. If your intention is e.g. to get a total "per day in the current MM/YY"YYY you know what to do - create two fields for the YYYY and MM you want to look at in the table header. Basically there is nothing wrong with using dates in a SUMIF directly, like in

=SUMIF($A$1:$A$15,DATE(2011,9,F1),$B$1:$B$15)

because at the end a date is a number.

Alternatively you may use a Pivot table which aggregatees by day whatever date you have in the list, and you can sort/filter on any key field.

Upvotes: 0

Excellll
Excellll

Reputation: 5785

You can also use an array formula:

=SUM(IF(DAY(Sheet1!$A$1:$A$11)=Sheet2!A2,Sheet1!$D$1:$D$11,0))

Enter the formula text with Ctrl+Shift+Enter.

Upvotes: 1

Bruno Leite
Bruno Leite

Reputation: 1477

Use this

=SUMPRODUCT((DAY(A1:A50)=A1)*(B1:B50))

Where
A1:A50 range of dates
A1 the day to find
B1:B50 The range of values to SUM

[]´s

Upvotes: 1

Related Questions