Excel: Sumif Multiple Columns based on the same Criteria

0
votes

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)

1
excel-formulasumifs
Without example data, I only have two suggestions: 1) have you tried SUMIFS(), 2) your sumproduct needs two arrays, perhaps replace the "*" with "," and I am not sure if the criteria will function as you expect on column A. It will be easier for anyone to help with an example. – Solar Mike
Hey @SolarMike, thank you for getting back to me I did try and add multiple images however due to repuation I am limited to 2 Links. So I have added a photograph of my raw data. So I tried SUMIFS with the following formula: =SUMIFS(B:E,A:A,"Rope: Hemp") It returned and #N/A error. I think because I am trying to sum multiple columns with the same Criteria. I also utilised the SUMPRODUCT formula as utilised above with an '*' and an ',' Which returned an #N/A and #VALUE. I don't see what I am doing wrong. – Matthew Perryman

1 Answers

1
votes

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