Formula Help

N

NeoSez

Board Regular
Joined
Aug 14, 2020
Messages
246
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
I have a the formula below. I want to change it so that it will include everything is under the same category as what is stated in column K.
Eg: Column "K" is Fruit and Column "F" will have apples, oranges, blueberries etc...
Currently, the formula will only pick up one item from column "F" instead of all related items.
Excel Formula: 
=Proper)Indirect("F"&MIN)IF(SUBTOTAL(3,OFFSET(F6,ROW(F6:K10000)-ROW(F6),,1)),ROW(F6:f10000)))))
 

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.
Sort by date Sort by votes
Can you post the real formula you are trying to use. The one above has errors such as
- too many closing brackets
- The wrong bracket after =PROPER and presumably MIN)IF

Also some sample data would help.
 
Upvote 0
myall_blues said:
Can you post the real formula you are trying to use. The one above has errors such as
- too many closing brackets
- The wrong bracket after =PROPER and presumably MIN)IF

Also some sample data would help.
Click to expand...
Excel Formula: 
=PROPER(INDIRECT("F"&MIN(IF(SUBTOTAL(3,OFFSET(F5,ROW(F5:F19988)-ROW(F5),,1)),ROW(F5:F19988)))))

Goal: Modify above formula to sum all FORD (Column "K") cars that are under column "F". Currently, it only sums up one type of car (Column "F").


TrackerDATESTATUSLOCATIONDESCRIPTIONCARDATE CLOSEDAgeCOMMENTSCATEGORYCOMPANY
writeoffF150TRUCKFORD
driveFLEXBUS
driveEDGESEDAN
 
Upvote 0
Would be beneficial if you provided 8-10 records to show different company scenarios. Additionally, would be beneficial if you mocked up your desired solution for the 8-10 records.
 
Upvote 0
alansidman said:
Would be beneficial if you provided 8-10 records to show different company scenarios. Additionally, would be beneficial if you mocked up your desired solution for the 8-10 records.
Click to expand...
Would this help?

1​
TrackerDATESTATUSLOCATIONDESCRIPTIONCARDATE CLOSEDAgeCOMMENTSCATEGORYCOMPANY
2​
1001​
1-May-24​
writeoffhomeThis is car AF150
12-May​
11​
TRUCKFORD
3​
1002​
2-May-24​
drivegarageThis is car AFLEX
26​
BUSFORD
4​
1003​
3-May-24​
driveroadThis is car AEDGE
25​
SEDANFORD
5​
1004​
4-May-24​
drivehomeThis is car AF150
24​
TRUCKFORD
6​
1005​
5-May-24​
drivegarageThis is car AFLEX
23​
BUSFORD
7​
1006​
6-May-24​
driveroadThis is car AF150
22​
TRUCKFORD
8​
1007​
7-May-24​
writeoffhomeThis is car AFLEX
13-May​
6​
BUSFORD
9​
1008​
8-May-24​
drivegarageThis is car AF150
20​
TRUCKFORD
10​
1009​
9-May-24​
driveroadThis is car AFLEX
19​
BUSFORD
11​
1010​
10-May-24​
drivehomeThis is car Aedge
18​
SEDANFORD
12​
1011​
1-Apr-24​
driveroadThis is car ARAM
57​
truckDODGE
13​
1012​
2-Apr-24​
driveroadThis is car ACharger
56​
SEDANDODGE
14​
1013​
3-Apr-24​
driveroadThis is car AHornet
55​
SUVDODGE
15​
1014​
4-Apr-24​
driveroadThis is car ARAM
54​
truckDODGE
16​
1015​
5-Apr-24​
driveroadThis is car ACharger
53​
SEDANDODGE
17​
1016​
6-Apr-24​
driveroadThis is car AHornet
52​
SUVDODGE
18​
1017​
7-Apr-24​
driveroadThis is car ARAM
51​
truckDODGE
19​
1018​
8-Apr-24​
driveroadThis is car ACharger
50​
SEDANDODGE
20​
1019​
9-Apr-24​
driveroadThis is car AHornet
49​
SUVDODGE
21​
1020​
10-Apr-24​
driveroadThis is car ACorolla
48​
SEDANTOYOTA
22​
1021​
11-Apr-24​
driveroadThis is car APrius
47​
SEDANTOYOTA
23​
1022​
12-Apr-24​
driveroadThis is car ATundra
46​
TRUCKTOYOTA
24​
1023​
13-Apr-24​
driveroadThis is car ACorolla
45​
SEDANTOYOTA
25​
1024​
14-Apr-24​
driveroadThis is car APrius
44​
SEDANTOYOTA
26​
1025​
15-Apr-24​
drivegarageThis is car ATundra
43​
TRUCKTOYOTA
 
Upvote 0
Is this what you mean?
Book1
ABCDEFGHIJKLMN
1TrackerDATESTATUSLOCATIONDESCRIPTIONCARDATE CLOSEDAgeCOMMENTSCATEGORYCOMPANYCompanyCount
210011-May-24writeoffhomeThis is car AF15012-May11TRUCKFORDFORD10
310022-May-24drivegarageThis is car AFLEX26BUSFORDDODGE9
410033-May-24driveroadThis is car AEDGE25SEDANFORDTOYOTA6
510044-May-24drivehomeThis is car AF15024TRUCKFORD
610055-May-24drivegarageThis is car AFLEX23BUSFORD
710066-May-24driveroadThis is car AF15022TRUCKFORD
810077-May-24writeoffhomeThis is car AFLEX13-May6BUSFORD
910088-May-24drivegarageThis is car AF15020TRUCKFORD
1010099-May-24driveroadThis is car AFLEX19BUSFORD
11101010-May-24drivehomeThis is car Aedge18SEDANFORD
1210111-Apr-24driveroadThis is car ARAM57truckDODGE
1310122-Apr-24driveroadThis is car ACharger56SEDANDODGE
1410133-Apr-24driveroadThis is car AHornet55SUVDODGE
1510144-Apr-24driveroadThis is car ARAM54truckDODGE
1610155-Apr-24driveroadThis is car ACharger53SEDANDODGE
1710166-Apr-24driveroadThis is car AHornet52SUVDODGE
1810177-Apr-24driveroadThis is car ARAM51truckDODGE
1910188-Apr-24driveroadThis is car ACharger50SEDANDODGE
2010199-Apr-24driveroadThis is car AHornet49SUVDODGE
21102010-Apr-24driveroadThis is car ACorolla48SEDANTOYOTA
22102111-Apr-24driveroadThis is car APrius47SEDANTOYOTA
23102212-Apr-24driveroadThis is car ATundra46TRUCKTOYOTA
24102313-Apr-24driveroadThis is car ACorolla45SEDANTOYOTA
25102414-Apr-24driveroadThis is car APrius44SEDANTOYOTA
26102515-Apr-24drivegarageThis is car ATundra43TRUCKTOYOTA
Sheet2
Cell Formulas
RangeFormula
M2:M4M2=UNIQUE(K2:K26)
N2:N4N2=COUNTIF(K2:K26,M2#)
Dynamic array formulas.
 
Upvote 0
Cubist said:
Is this what you mean?
Book1
ABCDEFGHIJKLMN
1TrackerDATESTATUSLOCATIONDESCRIPTIONCARDATE CLOSEDAgeCOMMENTSCATEGORYCOMPANYCompanyCount
210011-May-24writeoffhomeThis is car AF15012-May11TRUCKFORDFORD10
310022-May-24drivegarageThis is car AFLEX26BUSFORDDODGE9
410033-May-24driveroadThis is car AEDGE25SEDANFORDTOYOTA6
510044-May-24drivehomeThis is car AF15024TRUCKFORD
610055-May-24drivegarageThis is car AFLEX23BUSFORD
710066-May-24driveroadThis is car AF15022TRUCKFORD
810077-May-24writeoffhomeThis is car AFLEX13-May6BUSFORD
910088-May-24drivegarageThis is car AF15020TRUCKFORD
1010099-May-24driveroadThis is car AFLEX19BUSFORD
11101010-May-24drivehomeThis is car Aedge18SEDANFORD
1210111-Apr-24driveroadThis is car ARAM57truckDODGE
1310122-Apr-24driveroadThis is car ACharger56SEDANDODGE
1410133-Apr-24driveroadThis is car AHornet55SUVDODGE
1510144-Apr-24driveroadThis is car ARAM54truckDODGE
1610155-Apr-24driveroadThis is car ACharger53SEDANDODGE
1710166-Apr-24driveroadThis is car AHornet52SUVDODGE
1810177-Apr-24driveroadThis is car ARAM51truckDODGE
1910188-Apr-24driveroadThis is car ACharger50SEDANDODGE
2010199-Apr-24driveroadThis is car AHornet49SUVDODGE
21102010-Apr-24driveroadThis is car ACorolla48SEDANTOYOTA
22102111-Apr-24driveroadThis is car APrius47SEDANTOYOTA
23102212-Apr-24driveroadThis is car ATundra46TRUCKTOYOTA
24102313-Apr-24driveroadThis is car ACorolla45SEDANTOYOTA
25102414-Apr-24driveroadThis is car APrius44SEDANTOYOTA
26102515-Apr-24drivegarageThis is car ATundra43TRUCKTOYOTA
Sheet2
Cell Formulas
RangeFormula
M2:M4M2=UNIQUE(K2:K26)
N2:N4N2=COUNTIF(K2:K26,M2#)
Dynamic array formulas.
Click to expand...
Thank you for your reply.
I do have a formula in another box that relies on the first formula. If I can revise this one formula to encompass everything, that would be fantastic.
Excel Formula: 
=COUNTIFS(F:F,$E$1,G:G,">"&$H$1-1)
 
Upvote 0
I do not follow your formula. What is in row 1? Is it not the headers?
 
Upvote 0
Values in column F:F will never be equal to the header E1 *"Description", so it'll return 0.
 
Upvote 0
You must log in or register to reply here.

Similar threads

smide
  • Solved
Formula for percentile if data are in multiple columns
Replies
2
Views
101
hagia_sofia
hagia_sofia
drom
  • Question Question
SUMPRODUCT to count dependent validation list column with wrong items
Replies
1
Views
263
drom
drom
L
  • Question Question
I'm crashing Excel with AutoFilter.Apply
Replies
1
Views
221
Anthony47
A
G
  • Question Question
Improve calculation speed for this worksheet with INDIRECT/FILTER functions
Replies
0
Views
371
GeneBF
G
E
  • Solved
Formula not working
Replies
2
Views
316
esmarques
E

Forum statistics

Threads
1,223,893
Messages
6,175,240
Members
452,621
Latest member
Laura_PinksBTHFT

Share this page

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
Back
Top