The tricky part is dates, you need to get total by month, while you have dates, and not months, it does not seam to have a direct way<\/p>\n
Why SUMIF can’t help?<\/h3>\n
SUMIF does not help here, because it only works for one condition, we need two<\/p>\n
We need to get total of a certain column if date is between 1st of month, and end of month, which actually are two conditions and not one<\/p>\n
So, how can we do that?<\/h3>\n
When we have a table like below<\/p>\n
And we need to get the monthly total of amount, in column in G<\/p>\n SUMIFS is the way to do that, it was added in Excel2010, I would guess based on demand?<\/p>\n Anyways, SUMIFS works like SUMIF, but it can handle multiple conditions, up to 256, with AND.<\/p>\n Means it will get the total of a column, if (and only if) all conditions are met<\/p>\n The flexibility of SUMIFS gives us a lot of features, as we have it below, SUMIFS (along with other functions like DATE, YEAR, etc.) can get us the monthly total of amount.<\/p>\n If this complicated, we will break it down.<\/p>\n We are building the conditions inside SUMIFS, by saying…<\/p>\n If the date is more than or equal first of that month, and date is less than the 1st of next month, then get amount<\/p>\n So we created the two dates, 1st of this month, and 1st of next month, using DATE, with YEAR and MONTH as highlighted in screenshot<\/p>\n Then we used & to concatenate the criteria in Criteria1 and Criteria2<\/p>\n Then we asked SUMIFS to do the rest<\/p>\n Anytime you do changes, you can see those numbers updated as we expected<\/p>\n <\/p>\n Of course back in the day before SUMIFS we did another trick<\/p>\n Which is adding the month and year in a separate column, then do the Sum using SUMIF having condition on that column<\/p>\n But that should be in another article …<\/p>\n","protected":false},"excerpt":{"rendered":" Again, thanks to one of my students, I am getting another post with example \ud83d\ude42 I did promise her to post it after she sounded interesting in getting this example Here is an example on how to calculate total amounts of dollars per month (and number of days) from a table with dates The tricky…<\/p>\n![\"\"](\"http:\/\/xlfxs.com\/wp-content\/uploads\/2017\/12\/andor0.jpg\")
<\/a><\/p>\n=SUMIFS(E:E,D:D,\">=\"&DATE(YEAR(D5),MONTH(D5),1),D:D,\"<\"&DATE(YEAR(D5),MONTH(D5)+1,1))<\/code><\/p>\n<\/a><\/p>\n<\/a> <\/a><\/a><\/p>\nWhat if I cannot do SUMIFS?<\/h3>\n