Excel – Summing specific cells in excel with header that meets specific text criteria

microsoft excelmicrosoft-excel-2010worksheet-function

I have a specific problem like following:

enter image description here

As you can see I have 3 cells in which I have "Total" value of something.

I'd like to sum all of these values below, in whichever column there's a text that matches the word "Total".

So for instance there are 3 columns that contain word "Total" right now, and I'd like to sum their values below them which are:

1500, 11223 and 11220

I've tried using the sumif function in Excel, but for some reason it seems to be able only to sum a range of columns, not range of multiple rows…

I tried this:

=SUMIF(A2:AJ2,"*Total*",B3:AJ3)

B3:AJ3 are colums right below these upper ones...

Edit: The total value that I should get is 23943

Best Answer

My bad, not nearly as complicated as I was thinking

=SUMIF(A1:G1,"*total*",A2:G2)

Capitalization differences would need something like

=SUMIF(UPPER(A1:G1),"*TOTAL*",A2:G2)
Related Question