Reputation: 1827
Here's what I want to do.
I have the following table:
And another one that looks like this:
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
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
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
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