Multiplying a text cell by the number in another cell.

[Slyon]

Hi all,

I was wondering if anyone could assist me with my spreadsheet, as I'm struggling to search the way to do it anywhere online.

So, I've created a spreadsheet to log clothing items for a list of people, containing item type and the size/quantity of each item (see sheet 1 below).

What I want to do is add a separate sheet which works out the totals of everything on the other sheet, kind of like a tally chart if you want (see sheet 2 below).

I don't know if this is possible at all, but I need for example anything that is entered into each of the item sections for a certain size multiplied by it's quantity (if more than one) then filtered into the relevant cell on sheet 2 depending on what it the cells on sheet 1 contain.

*For example from the below, whatever is inputted into each item section for each person, so in this case cell B4 multiply it by it's quantity in cell C4, resulting in the total of 2 populating into sheet 2 in cell E4. I'm thinking if excel can pick up what text is in each cell then filter it to the relevant cells in sheet 2.

Apologies if this doesn't make much sense, I'm really bad at explaining it in writing! ?‍

All help is really appreciated and if what I'm asking isn't doable then my apologise at least I can scrap that idea. Thank you!

SHEET 1

SHEET 2

 

[RoryA]

Welcome to the forum.

You basically want SUMIF formulas like this, for item1

=SUMIF(Sheet1!$B:$B,"32R",Sheet1!C:C)

 

[jasonb75]

For Item 1 try entering

=SUMIF(Sheet1!$B:$B,C$3,Sheet1!$C:$C)

into C4, then filling it right as far as K4.
Then try the same method for the other items, editing the formula as needed.

One thing to note is that it needs to be identical in both sheets, you currently have "32R" in one sheet and "32 R" in the other. The space between 32 and R in sheet 2 means that they are not a match, so you will not get the correct result.

 

[Slyon]

Welcome to the forum.

You basically want SUMIF formulas like this, for item1

=SUMIF(Sheet1!$B:$B,"32R",Sheet1!C:C)

Thanks for replying! I've tried this but seem to get the following value error..

Am I doing something wrong?

 

[Slyon]

For Item 1 try entering

=SUMIF(Sheet1!$B:$B,C$3,Sheet1!$C:$C)

into C4, then filling it right as far as K4.
Then try the same method for the other items, editing the formula as needed.

One thing to note is that it needs to be identical in both sheets, you currently have "32R" in one sheet and "32 R" in the other. The space between 32 and R in sheet 2 means that they are not a match, so you will not get the correct result.

Thanks for explaining that for me, much appreciated.

I've tried the formula you gave also and as per the reply above I seem to be getting a value error too.

I ofcourse could be doing something wrong?

 

[RoryA]

Do you have any #VALUE! errors on Sheet1?

 

[Slyon]

Do you have any #VALUE! errors on Sheet1?

Not that I can see no. There's no formulas or any conditional formatting on either of the 2 sheets (as of yet).

 

[jasonb75]

Do you have any #VALUE! errors on Sheet1?

That wouldn't make a difference anyway, SUMIF ignores such errors in the source. It does, however, return a #VALUE! error instead of a #REF! error if the source sheet name is incorrect.

I ofcourse could be doing something wrong?

As we couldn't see the actual sheet names in your screen captures, we have used the default Sheet1 as an example (in line with the names shown in your description). You would need to change that to the actual name of your sheet if it is something different.

 

[RoryA]

That wouldn't make a difference anyway, SUMIF ignores such errors in the source

Really? Did you test that with a #VALUE error in the data range for a row that matches the criteria? For me, that produces a #VALUE! error.

 

[jasonb75]

Did you test that with a #VALUE error in the data range for a row that matches the criteria?

I did create some deliberate #VALUE! errors in a sheet that I was working on at the time but did not check if they were in rows that met the sumif criteria, only that they were in the function ranges. (Your earlier reply didn't specify rows meeting the criteria as a requirement).