CODEWITHSUNDEEP
excel-formulasumifs
Matthew Perryman
Matthew Perryman

Reputation: 129

Excel: Sumif Multiple Columns based on the same Criteria

So I am trying to create a spreadsheet at allows a character in game to total the party inventory.

I am trying to sumif multiple columns based on the same criteria.

So say I am trying to sum all the rope they have.

In column A is the Item descriptions In columns B-E are the different totals for each party member (one column per person)

Each party member has 50 rope, so I am expecting 200 rope. I have used this formula:

=SUMIF(A:A,"Rope: Hemp",B:E) and it is only returning 50 as a value, If I utilise cell values (A1:A100 etc.) it returns a value of Zero.

I have been told that Sum Product could works so I also tried that:

=SUMPRODUCT((A1:A100="Rope: Hemp")*(B3:E100)) and I still get the incorrect result.

What am I doing wrong?

EDIT: Here are some photos. Here is my Raw Data. As you can see I have the inventory and tallies, when you look at rope it says 150, and this was calcualted by summing the B to E cells, however as the list is going to move and grow I thought SUMIF would be better.

As stated above, I have used a SUMIF making the range Columns B through E, and it only returns a value of 50 (I'm assuming Column B)

Upvotes: 0

Views: 4376

Answers (1)

Solar Mike
Solar Mike

Reputation: 8365

so came up with this: =SUMIF(A3:A40,"="&J17&"",B3:B40)+SUMIF(A3:A40,"="&J17&"",C3:C40)+SUMIF(A3:A40,"="&J17&"",D3:D40)+SUMIF(A3:A40,"="&J17&"",E3:E40)

Which works, but I can only assume that sumif only work with ONE target range... And I tried curly brackets as well...

So, I did also use cell J17 for the object you are looking for, so you can drag it down, the "*" will find all occurences of "Rope: Hemp", or "Rope: Nylon" etc. I get a total of 150 as there are only 3 characters with rope...

Hope it helps. Someone else may have a better / neater suggestion!

I just tested it by entering Rope, rapier and rations into J17 and got the results I expect. Image of spreadsheet: based on your layout

Upvotes: 1

Related Questions