CODEWITHSUNDEEP
excel-formula
user4815703
user4815703

Reputation:

Can I use SUMIF with OFFSET to get my totals?

Hi so I have the data at follows:

  1. Alex 300 400
  2. Sarah 200 400
  3. Total 500 800
  4. John 700 300
  5. Alex 200 300
  6. Total 900 600

I am thinking I need to search the text total and sum the cell on the right of it. But I only want the totals. This list may be added to. Additionally I want to grab the second column as well as a separate formula hence why I’m thinking using offset.

Added Info:

From the below example i may have multiple Job Blocks with a Total field. What i wish to achieve is search all cells in Column A for text "Total", then SUM all green values in Column B. Separately i wish to also do the same with Blue fields in Column C using Total as an anchor point.

I hope this clarifies this a little.

Example of sheet

Upvotes: 0

Views: 2264

Answers (1)

EDS
EDS

Reputation: 2195

No need for OFFSET. Just use SUMIF.

For example, if you have the resulting formulas below your data, just use:

=SUMIF($A$1:$A$100, "Total", B$1:B$100) and drag to the right.

If you have the newest version of Excel, you can also use FILTER like

=SUM(FILTER($B1:B$100, $A$1:$A$100="Total"))

If you want the results stored in a way that isn't right below the data, and want to drag the data, I prefer INDIRECT. =SUMIF($A$1:$A$100, "Total", INDIRECT(ADDRESS(ROW(B1),COLUMN(B1))&":"&ADDRESS(ROW(B100), COLUMN(B100))))

Upvotes: 0

Related Questions