Calculate Data By Certain Criteria

As a powerful spreadsheet application, Microsoft Excel has many formulas to help us to create many report models. No doubt about it. Including formula which has function to calculate a range data with one or more criteria. Before Microsoft Excel 2007, the earlier version of Excel has SUMIF function to do that job.

In my example file, I have sample my expenses note that has date data on A4:A16 and sum range on C4:C16. And I want to calculate how much my money has spent from day 1 to 7 on this month. If I am using SUMIF, I have to write the formula like this:

=SUMIF(A4:A16;1;C4:C16) + SUMIF(A4:A16;2;C4:C16)+ SUMIF(A4:A16;3;C4:C16)+ SUMIF(A4:A16;4;C4:C16)+ SUMIF(A4:A16;5;C4:C16) + SUMIF(A4:A16;6;C4:C16) + SUMIF(A4:A16;7;C4:C16)

You can also write it with criteria “>=7″ but how does about criteria from day 8 to 14? You have to write it one by one like my example above. Thanks to Excel 2007, now it came with SUMIFS formula. With SUMIFS, we can simplify our formula write. For example, I want calculate the data from day 8 to 14, instead of I write it one by one per criteria with SUMIF formula, write it like this using SUMIFS formula:

=SUMIFS($C$4:$C$16;$A$4:$A$16;">=8";$A$4:$A$16;"<=14")

I think it is more simple than SUMIF. And you can put your criteria more than one, you can write the criteria like this:

SUMIFS(sum_range; range_kriteria_1; kriteria_1; range_kriteria_2; kriteria_2... dst)

Hopefully, this article may help you…

Leave a Comment