# Summing specific cells in excel with header that meets specific text criteria

I have a specific problem like following: 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

• Why not just use the column letters? Otherwise you'll need an index/match type thing. e.g. `\$N1=\$D1+\$G1+\$L1` – Raystafarian Oct 12 '16 at 11:36
• You mean like T and stuff like that? But there's another problem regarding the summing thingy... The sumif function for some reason isn't able to sum multiple rows, only 1 column within a given specific range... – perkes456 Oct 12 '16 at 11:38
• Are you summing per row or for the total? Why not use subtotals then? – Raystafarian Oct 12 '16 at 11:39
• Hi @Raystafarian because I want the process to be automated whenever I put a new cell with text of "Total", I want it to be automatically summed for the given range ... – perkes456 Oct 12 '16 at 11:39
• New cell per row or insert a new column? – Raystafarian Oct 12 '16 at 11:39

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)
``````
• I tried using this, and it wasn't this one ...I found a solution by using the sumproduct function – perkes456 Oct 12 '16 at 11:59
• Make sure you adjust it for your range. If your capitalization is different across columns, you should use the `UPPER` to get the matches. – Raystafarian Oct 12 '16 at 12:11
• This is it. Ty !!! :) – perkes456 Oct 12 '16 at 16:17
``````{=sum(index(whole grid,,match("*total*", headers,0)))}
``````

You can sum entire rows/columns if you use index and one match function. You cannot index/match both row and column because then it will just take the first one of each.