Multiplying a text cell by the number in another cell.

Slyon

New Member
Joined
Jul 26, 2021
Messages
4
Office Version
  1. 2016
Platform
  1. Windows
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! :ROFLMAO:🙆‍♀️

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
1627294391090.png


SHEET 2
1627293596546.png
 

Attachments

  • 1627293464828.png
    1627293464828.png
    39.5 KB · Views: 2
  • 1627294355465.png
    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
Joined
May 2, 2008
Messages
37,362
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
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
Joined
Dec 30, 2008
Messages
13,549
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Jul 26, 2021
Messages
4
Office Version
  1. 2016
Platform
  1. Windows
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?

1627296578850.png
 

Slyon

New Member
Joined
Jul 26, 2021
Messages
4
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

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
Joined
May 2, 2008
Messages
37,362
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
Do you have any #VALUE! errors on Sheet1?
 

Slyon

New Member
Joined
Jul 26, 2021
Messages
4
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

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
Joined
Dec 30, 2008
Messages
13,549
Office Version
  1. 365
Platform
  1. Windows
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
Joined
May 2, 2008
Messages
37,362
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
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
Joined
Dec 30, 2008
Messages
13,549
Office Version
  1. 365
Platform
  1. Windows
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).
 

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top