Tag: Excel

Posted on

Named Ranges in Intermediate Excel Make Calculations Easier

I wish I could brag about how named ranges help me professionally.  They do, but I don’t have a cute story about it.  However, it is easier for me to set up tables and formulas when I think about what each cell’s contents represent rather than the actual cell references.  Seriously, Z3432?  Who cares?  I want to know what it means.

Named ranges also come in handy when you start working with the table objects in Excel.  This concept makes structured referencing that much easier.

Wanna learn how?

Excel Intermediate

Excel Intermediate is a six-week course starting:

Tuesday, January 4, 2022

7:00-8:00 pm EST

Sign Me Up!!!!
Posted on

NIFTY TIP Adding or Subtracting Amounts from Different Ranges

Problem: You need to add or subtract amounts from different ranges according to a specific criteria (SUMIF function).

Solution: Insert two SUMIF formulas and combine them into a single formula:

  1. Insert the SUMIF formula to total the amounts based on the criterion 701 into cell E2.
  2. Insert the SUMIF formula to total the amounts based on the criterion 300 into cell E3.
  3. Select cell E2 and copy the formula from the Formula Bar, select the formula and press <ctrl>+C and click the Enter or Cancel symbol in the Formula Bar to exit Edit mode.
  4. Select cell E5 and press <ctrl>+V.
  5. Select cell E3 and copy the formula from the Formula Bar without the equals (=) sign by selecting the formula and pressing <ctrl>+C.
  6. Select cell E5 and enter a minus(-) sign after the formula in the cell, and then press <ctrl>+V.

The combined formula is now:

=SUMIF(A:A,D2,B:B)-SUMIF(A:A,D3,B:B)