1

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

  • 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
2

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
0
{=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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.