Formula to Sum Specific Amounts

Options

Putting it mildly, I don't understand formulas. I'd like a formula that will total all amounts when the word "Billed" is in the same row. Then another formula for when the word "Originated" is in the same row.

For example, every time I add a new month's totals, I'd like the YTD totals to update. I tried this, but I really don't know what I'm doing: =SUMIF(Month B & O:Month B & O; Billed:Billed) It's #UNPARSEABLE.

I'm attaching a screen shot. Please let me know if this needs more explaining. Thank you in advance.


Tags:

Best Answer

  • Sameer Karkhanis
    Sameer Karkhanis ✭✭✭✭✭✭
    edited 02/10/22 Answer ✓
    Options

    You are probably looking for something like below,

    =SUMIF([Month B & O]1:[Month B & O]5, [Month B & O]@row, MV1:MV5) -- You can use this for both the billed and originated rows. (If you drag the formula MV will be replaced by the column name. If you copy paste then manually replace with appropriate column name). In this case, if you insert additional rows above the YTD Totals row then the formula will adjust automatically and give you the sum you are looking for.

    Since you are using the same value (aka Billed and Originated) in the rows under YTD totals you cannot use the entire column range. If you do want to use the column range then you may want to specify values"Billed Totals" and "Originated Totals" instead as the cell values in rows under YTD Totals. In that case you can use,

    =SUMIF([Month B & O]:[Month B & O], "Billed", MV:MV) and =SUMIF([Month B & O]:[Month B & O], "Originated", MV:MV)

Answers

  • Sameer Karkhanis
    Sameer Karkhanis ✭✭✭✭✭✭
    edited 02/10/22 Answer ✓
    Options

    You are probably looking for something like below,

    =SUMIF([Month B & O]1:[Month B & O]5, [Month B & O]@row, MV1:MV5) -- You can use this for both the billed and originated rows. (If you drag the formula MV will be replaced by the column name. If you copy paste then manually replace with appropriate column name). In this case, if you insert additional rows above the YTD Totals row then the formula will adjust automatically and give you the sum you are looking for.

    Since you are using the same value (aka Billed and Originated) in the rows under YTD totals you cannot use the entire column range. If you do want to use the column range then you may want to specify values"Billed Totals" and "Originated Totals" instead as the cell values in rows under YTD Totals. In that case you can use,

    =SUMIF([Month B & O]:[Month B & O], "Billed", MV:MV) and =SUMIF([Month B & O]:[Month B & O], "Originated", MV:MV)

  • Nancy Mayberry
    Options

    Sameer!

    This works perfectly! Thank you so much for your time and thoughtful explanation! I learned a lot!

    Have a great day!

    Nancy

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!