How can I SUMIF a cell if another cell matches a cell on multiple pages?

user_13062685 0
Viewed 33

What I am trying to do is set up a total value of a cell that is associated with a particular shop on multiple pages.

I have 20 different sheets of products for instance.

I want to sum cell I2 if cell H2 is the same on my formula sheet's B13.

I have sheet1:sheet20!H2 has to be the same as total!B13(the sheet where the formula is) and I am summing the value of sheet1:sheet20!I2

In my logic I wrote this:

=SUMIF(SHEET1:SHEET20!H2,B13,SHEET1:SHEET20!I2)

But sadly, my logic doesn't seem to give an answer.

B12 will have the sum of all the I2 cells throughout the 20 sheets. B13 would have the shop name "Shop Y" for example. SHEET1:SHEET20!H2 on the individual sheets will also have that shop name "Shop Y". SHEET1:SHEET20!I2 will have how much money was spent at "Shop Y"

And then this will sum up all money spent at Shop Y on various products through the 20 sheets.

Then I will do the same for Shop X etc (but this will just need a copy and paste).

excel
1 Answers

=SUM(FILTERXML("<a><b>"&TEXTJOIN("</b><b>",,Sheet1:Sheet20!H2:I2)&"</b></a>","//b[preceding::*[1]="""&total!B13&"""]"))

The part

"<a><b>"&TEXTJOIN("</b><b>",,Sheet1:Sheet20!H2:I2)&"</b></a>"

creates a string whose delimited substrings comprise the entries from Sheet1!H2, Sheet1!I2, Sheet2!H2, Sheet2!I2, etc., in that order.

By passing this string to FILTERXML with an appropriate xpath parameter (which, in this case, checks whether the preceding entry in that delimited string is equal to the entry in total!B13 or not), we obtain the desired sum.

This set-up could fail if the entry in total!B13 were numeric. From your description, however, I assumed that this was not the case.

user_170077040
Related