CODEWITHSUNDEEP
excel
Ed Jones
Ed Jones

Reputation: 341

Sum based on text in above cell

Trying to find a solution for a way to total up the value in cells based on whether the value above that cell contains a specific word. For example, imagine a spreadsheet as follows:

dog  chicken  dog  cat  dog  chicken
 1      2      1    2    4      2

dog  chicken  dog  cat  dog  chicken
 11     4      6    2    1      3

I will have a series of rows like above, maybe 10 or so.

I will then have a summary page that is like this:

dog:
chicken:
cat:

What I'd like to do, is total up all of the rows in the previous sheet based on what text is above them. So in the instance of "dog" id have a total of 24.

Hope that all makes sense. I'm struggling to get my head round how I can achieve this in Excel. Any tips or ideas?

Thanks!

Upvotes: 0

Views: 336

Answers (1)

BigBen
BigBen

Reputation: 49998

Use SUMIFS (or just SUMIF) with offset ranges, e.g.:

=SUMIFS(A2:F5,A1:F4,"dog")

or

=SUMIF(A1:F4,"dog",A2:F5)

enter image description here

Upvotes: 4

Related Questions