Determining amounts when a cell may represent more than one item

IamBatman

New Member
Joined
Jan 20, 2022
Messages
9
Office Version
  1. 365
  2. 2013
Platform
  1. Windows
I'm not quite sure how to explain this properly (and the title is probably rubbish), so please also look at the picture.

I have a list of dimensions from projects that I've combined into one spreadsheet. I need to be able to see what size pieces we end up with the most, but some of the lines represent more than one piece.

For example. If I want to determine how many items have 7 as one of their dimensions, I can use countif(C:E,"7") (except I usually use a cell reference instead of the actual dimension). From the set shown, I'll get back 4, but there are really 8 pieces (quantities of 2, 1, 2, and 3). How do I account for the item numbers in column B?

I thought briefly about manually correcting this (insert line(s), copy the dimensions, and change # of items to one), but 536 of 930 lines have more than 1 item in the design, and 22 have 10 or more items in the design. I wouldn't mind them showing like that (quantity of 1), but I'm not taking that much time. I'm also fine with using something besides countif. I'm really just looking for something simple (I can set it up once and keep adding data) and as automated as possible.

Does anyone have any suggestions?
Thanks
 

Attachments

  • Portion of data.PNG
    Portion of data.PNG
    19.7 KB · Views: 12

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

jtakw

Well-known Member
Joined
Jun 29, 2014
Messages
7,245
Office Version
  1. 2016
Platform
  1. Windows
Hi,

May be this might do what you want:

Book3.xlsx
ABCDEFG
1Look forTotal Count
2211710178
32125102
41137103
51144104
61153105
71162106
81171107
92187108
102198109
112209110
1222110111
1322211112
143237113
153242114
Sheet1027
Cell Formulas
RangeFormula
G2G2=SUMPRODUCT((B2:E100=F2)*A2:A100)
 
Solution

BSALV

Banned user
Joined
Oct 31, 2010
Messages
1,651
Office Version
  1. 365
  2. 2013
  3. 2007
if it was arranged in a table (here tabel1)
Map1
BCDEFG
1BCDE
2175
317
417
515
62777
7
8
9
10
Blad1
Cell Formulas
RangeFormula
G2G2=SUM((COUNTIF(OFFSET(Tabel1[[#Headers],[C]:[E]],SEQUENCE(ROWS(Tabel1[B])),,,),7)>0)*Tabel1[B])
 

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
12,496
How about:

Book1
ABCDEFGHIJK
1# of itemsWidthHeightDepthDim
2691011
3191011DimensionColumnCountAll columns
4291077Height811
5171011
6191011
7291011
8291011
9491011
1029711
11291011
12191011
13191011
14191011
1519711
16191011
17491011
1829711
19591011
2039711
21291011
22491011
23291011
24191011
25191011
26191011
27191011
28191011
29191011
30491011
31591011
Sheet2
Cell Formulas
RangeFormula
I4I4=SUMPRODUCT(A2:A31*(INDEX(B2:D31,0,MATCH(H4,B1:D1,0))=G4))
K4K4=SUMPRODUCT(A2:A31*SIGN(MMULT(--(B2:D31=G4),{1;1;1})))
 

BSALV

Banned user
Joined
Oct 31, 2010
Messages
1,651
Office Version
  1. 365
  2. 2013
  3. 2007
#2 vs #3 : if you have in the same row 2 or 3 7's, do you have to calculate that as 1 or as 2 or 3 ?
 

IamBatman

New Member
Joined
Jan 20, 2022
Messages
9
Office Version
  1. 365
  2. 2013
Platform
  1. Windows
Hi,

May be this might do what you want:

Book3.xlsx
ABCDEFG
1Look forTotal Count
2211710178
32125102
41137103
51144104
61153105
71162106
81171107
92187108
102198109
112209110
1222110111
1322211112
143237113
153242114
Sheet1027
Cell Formulas
RangeFormula
G2G2=SUMPRODUCT((B2:E100=F2)*A2:A100)
I altered it slightly (added $ to the ranges, and went through 10000 instead of 100).

Works beautifully and simply. Thank you!

And thank you all for all the options.
 

BSALV

Banned user
Joined
Oct 31, 2010
Messages
1,651
Office Version
  1. 365
  2. 2013
  3. 2007
i preferred #4 as solution, simple !
What happens if there are multiple 7's in those 3 cells, is the result still okay ?
 

IamBatman

New Member
Joined
Jan 20, 2022
Messages
9
Office Version
  1. 365
  2. 2013
Platform
  1. Windows
i preferred #4 as solution, simple !
What happens if there are multiple 7's in those 3 cells, is the result still okay ?
You mean like 77? Or something else?


I've had issues with index and match sometimes, so I tend to avoid it.

This sheet won't be used tons. It was used to attempt to prove a point by showing what the data is, and may be used thusly again, but it's definitely not in daily use.
 

BSALV

Banned user
Joined
Oct 31, 2010
Messages
1,651
Office Version
  1. 365
  2. 2013
  3. 2007
column E = manual calculation and sum in J4
H4 = Erik W
I4 = Jtakw
the problem is row 4, multiple 7's in the same row !
For Erik W those 3 7's count as 1, for Jtakw as 3, multiplied by 2 (cell A4) is that 2 or 6 ???
Is it possible to have multiple 7's in a row ?
Batch_Example.xlsx
ABCDEFGHIJ
1# of itemsWidthHeightDepthformula
26910110
31910110DimensionErikWJtakwcheck
4277727121612
51710111
6197111
72910110
82910110
94910110
10297112
112910110
121910110
131910110
141910110
15197111
161910110
174910110
18297112
195910110
20397113
212910110
224910110
232910110
241910110
251910110
261910110
271910110
281910110
291910110
304910110
315910110
32
Blad2
Cell Formulas
RangeFormula
H4H4=SUMPRODUCT(My_Items*SIGN(MMULT(--(My_Sizes=G4),{1;1;1})))
I4I4=SUMPRODUCT((My_Sizes=G4)*My_Items)
J4J4=SUM(E2:E31)
E2:E31E2=A2*(COUNTIF(A2:D2,$G$4)<>0)
Named Ranges
NameRefers ToCells
My_Items=Blad2!$A$2:$A$31H4:I4, E2
My_Sizes=Blad2!$B$2:$D$31E2, H4:I4
Cells with Conditional Formatting
CellConditionCell FormatStop If True
B2:F31Expression=$G$4=B2textNO
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
56,572
Office Version
  1. 365
Platform
  1. Windows
I was also wondering about the same dimension occurring in multiple columns on the same row.

22 03 02.xlsm
ABCDEFGH
1# of itemsWidthHeightDepth
2691111
31111011DimensionItems
4277776
517108119
61978
729107
8291011
9
Count Items
Cell Formulas
RangeFormula
H4:H5H4=SUM(FILTER(A$2:A$10000,(B$2:B$10000=G4)+(C$2:C$10000=G4)+(D$2:D$10000=G4),0))
 

Forum statistics

Threads
1,176,089
Messages
5,901,338
Members
434,886
Latest member
qazibelal

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