# Multiplying a text cell by the number in another cell.

#### Slyon

##### New Member
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

#### Attachments

• 1627293464828.png
39.5 KB · Views: 2
• 1627294355465.png
34.9 KB · Views: 3

### Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.

#### RoryA

##### MrExcel MVP, Moderator
Welcome to the forum.

You basically want SUMIF formulas like this, for item1

Excel Formula:
``=SUMIF(Sheet1!\$B:\$B,"32R",Sheet1!C:C)``

#### jasonb75

##### Well-known Member
For Item 1 try entering
Excel Formula:
``=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

##### New Member
Welcome to the forum.

You basically want SUMIF formulas like this, for item1

Excel Formula:
``=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

##### New Member

For Item 1 try entering
Excel Formula:
``=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

##### MrExcel MVP, Moderator
Do you have any #VALUE! errors on Sheet1?

#### Slyon

##### New Member

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

##### Well-known Member
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

##### MrExcel MVP, Moderator
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

##### Well-known Member
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).

Replies
3
Views
56
Replies
4
Views
510
Replies
2
Views
225
Replies
11
Views
154
Replies
22
Views
655

1,141,618
Messages
5,707,430
Members
421,508
Latest member
someinternetuser

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back