Excel Magic Trick #145 p2 MODE IF

[fattyfattyporkface]

Thanks for this video. It really helped me out.

[ExcelIsFun]

You are welcome!

[thuynguyenable]

In excel Magic Trick #31,Part 2, you used

the sum(if...) to add the revenue for each department. I tried to use SUMIF to work on it but it gave me the wrong result.

I guessed I put the right formula for my SUMIF.

Range (vertical cell ranges of Dept01-02)

Criteria (Dept name in the validation list)

Sum Range ( the horizontal cell ranges of JanRev-MarRev)

It only gave the JanRev instead of adding the Rev from Jan-Mar. How do you do it with SUMIF and instead of SUM(IF..). Thanks.

[ExcelIsFun]

The answer depends on what is in thedate column. Are there words like Jan and Mar, or are there dates like 1/2/2010, 2/12/2010, etc.?

Also, the type of efficient answer that might be best depends on what version of Excel you have.

SUMIF will not do multiple criteria.

Depending on the answers from above, SUMIFS might be best for Excel 2007 - 2010 and SUMPRODUCT would be good for erlier versions.

[ExcelIsFun]

These videos show various solutions:

Excel Magic Trick 401: Quarterly Comparative Report - Pivot Table (PivotTable)

Excel Magic Trick 405: Quarterly Comparative Report SUMPRODUCT function

Excel Magic Trick 404: Quarterly Comparative Report SUMIFS function

Excel Magic Trick 403: March End Quarterly Comparative Report - Pivot Table