Unable to get formula to work correctly :(

J

JB2385

New Member
Joined
Apr 26, 2023
Messages
11
Office Version
  1. 365
Platform
  1. Windows
Hello!,

I posted something before on this formula and thankfully @Fluff was able to save the day. I had to adjust my table and rearrange my formulas. I was able to adjust the formula to work in the Table 2 in my new example. However, the formula used in Table 0 F:17 I was not able to get working in Table 1 B:28. Please look at my tables below and let me know what I am doing wrong. I appreciate all the help!!!!

Vehicle Inventory 6.xlsx
ABCDEFG
1
2DISTRICTPARKING SLOT #VINMAKEMODEL
34TH BN120JH4KA3170LC006787Chevrolet2.6
41ST BN11JS1RF16C442100152Ford 1.1Type III
54TH BN122C4GM68475R667819DodgeORV
62ND BN651P4GH44R0RX359386Chevrolet2.1
72ND BN121P4GH44R0RX359386ToyotaORV II
83RD BN11GBJ7D1B4BV132373Ford 1.0Type III
93RD BN66JH4KA2640GC004861ToyotaORV II
102ND BN41JH4CC2640NC004693ToyotaORV II
11
12ORIGINAL EXAMPLE
13
14
15TABLE 0
16ChevroletToyotaDodgeFord 1.0Ford 1.1 Type III
171ST BN00000
182ND BN12000
193RD BN01010
204TH BN10100
21
22NEW EXAPMPLE
23
24
25TABLE 1
26TYPES OF FORDS
271ST BN2ND BN3RD BN4TH BN
281.00
29Type III
30
31TABLE 2
32OTHER VEHICLES
331ST BN2ND BN3RD BN4TH BN
34Chevrolet0101
35Toyota0210
36Dodge0001
37
Sheet1
Cell Formulas
RangeFormula
B17:E17B17=IF(AND(B$16<>"",$A17<>""),SUMPRODUCT((SUBSTITUTE($A$3:$A$10,"ST BN","")=TRIM(SUBSTITUTE($A17,"ST BN","")))*($D$3:$D$10=B$16)),"")
F17F17=B263
B18:E18B18=IF(AND(B$16<>"",$A18<>""),SUMPRODUCT((SUBSTITUTE($A$3:$A$10,"ND BN","")=TRIM(SUBSTITUTE($A18,"ND BN","")))*($D$3:$D$10=B$16)),"")
F18F18=IF(AND(F$16<>"",$A18<>""),SUMPRODUCT((SUBSTITUTE($A$3:$A$10,"ND BN","")=TRIM(SUBSTITUTE($A18,"ND BN","")))*($D$3:$D$10="ford 1.1")*($E$3:$E$10=TRIM(SUBSTITUTE($F16,"Ford 1.1","")))),"")
B19:E19B19=IF(AND(B$16<>"",$A19<>""),SUMPRODUCT((SUBSTITUTE($A$3:$A$10,"RD BN","")=TRIM(SUBSTITUTE($A19,"RD BN","")))*($D$3:$D$10=B$16)),"")
F19F19=IF(AND(F$16<>"",$A19<>""),SUMPRODUCT((SUBSTITUTE($A$3:$A$10,"RD BN","")=TRIM(SUBSTITUTE($A19,"RD BN","")))*($D$3:$D$10="ford 1.1")*($E$3:$E$10=TRIM(SUBSTITUTE($F16,"Ford 1.1","")))),"")
B20:E20B20=IF(AND(B$16<>"",$A20<>""),SUMPRODUCT((SUBSTITUTE($A$3:$A$10,"TH BN","")=TRIM(SUBSTITUTE($A20,"TH BN","")))*($D$3:$D$10=B$16)),"")
F20F20=IF(AND(F$16<>"",$A20<>""),SUMPRODUCT((SUBSTITUTE($A$3:$A$10,"TH BN","")=TRIM(SUBSTITUTE($A20,"TH BN","")))*($D$3:$D$10="ford 1.1")*($E$3:$E$10=TRIM(SUBSTITUTE($F16,"Ford 1.1","")))),"")
B28B28=IF(AND(A$28<>"",$B27<>""),SUMPRODUCT((SUBSTITUTE($A$3:$A$10,"ST BN","")=TRIM(SUBSTITUTE($B27,"ST BN","")))*($D$3:$D$10="ford")*($E$3:$E$10=TRIM(SUBSTITUTE($A28,"Ford","")))),"")
B34B34=IF(AND(A$34<>"",$B33<>""),SUMPRODUCT((SUBSTITUTE($A$3:$A$10,"ST BN","")=TRIM(SUBSTITUTE($B33,"ST BN","")))*($D$3:$D$10=A$34)),"")
C34C34=IF(AND(A$34<>"",$C33<>""),SUMPRODUCT((SUBSTITUTE($A$3:$A$10,"ND BN","")=TRIM(SUBSTITUTE($C33,"ND BN","")))*($D$3:$D$10=A$34)),"")
D34D34=IF(AND(A$34<>"",$D33<>""),SUMPRODUCT((SUBSTITUTE($A$3:$A$10,"RD BN","")=TRIM(SUBSTITUTE($D33,"RD BN","")))*($D$3:$D$10=A$34)),"")
E34E34=IF(AND(A$34<>"",$E33<>""),SUMPRODUCT((SUBSTITUTE($A$3:$A$10,"ND BN","")=TRIM(SUBSTITUTE($E33,"ND BN","")))*($D$3:$D$10=A$34)),"")
B35B35=IF(AND(A$35<>"",$B33<>""),SUMPRODUCT((SUBSTITUTE($A$3:$A$10,"ST BN","")=TRIM(SUBSTITUTE($B33,"ST BN","")))*($D$3:$D$10=A$35)),"")
C35C35=IF(AND(A$35<>"",$C33<>""),SUMPRODUCT((SUBSTITUTE($A$3:$A$10,"ND BN","")=TRIM(SUBSTITUTE($C33,"ND BN","")))*($D$3:$D$10=A$35)),"")
D35D35=IF(AND(A$35<>"",$D33<>""),SUMPRODUCT((SUBSTITUTE($A$3:$A$10,"RD BN","")=TRIM(SUBSTITUTE($D33,"RD BN","")))*($D$3:$D$10=A$35)),"")
E35E35=IF(AND(A$35<>"",$E33<>""),SUMPRODUCT((SUBSTITUTE($A$3:$A$10,"TH BN","")=TRIM(SUBSTITUTE($E33,"TH BN","")))*($D$3:$D$10=A$35)),"")
B36B36=IF(AND(A$36<>"",$B33<>""),SUMPRODUCT((SUBSTITUTE($A$3:$A$10,"ST BN","")=TRIM(SUBSTITUTE($B33,"ST BN","")))*($D$3:$D$10=A$36)),"")
C36C36=IF(AND(A$36<>"",$C33<>""),SUMPRODUCT((SUBSTITUTE($A$3:$A$10,"ND BN","")=TRIM(SUBSTITUTE($C33,"ND BN","")))*($D$3:$D$10=A$36)),"")
D36D36=IF(AND(A$36<>"",$D33<>""),SUMPRODUCT((SUBSTITUTE($A$3:$A$10,"RD BN","")=TRIM(SUBSTITUTE($D33,"RD BN","")))*($D$3:$D$10=A$36)),"")
E36E36=IF(AND(A$36<>"",$E33<>""),SUMPRODUCT((SUBSTITUTE($A$3:$A$10,"ND BN","")=TRIM(SUBSTITUTE($E33,"ND BN","")))*($D$3:$D$10=A$36)),"")
Cells with Data Validation
CellAllowCriteria
A3:A10List--, 1ST BN, 2ND BN, 3RD BN, 4TH BN,
A17:A20List--, 1ST BN, 2ND BN, 3RD BN, 4TH BN,
B27:E27List--, 1ST BN, 2ND BN, 3RD BN, 4TH BN,
B33:E33List--, 1ST BN, 2ND BN, 3RD BN, 4TH BN,
 
Last edited by a moderator:

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.
Sort by date Sort by votes
I do not understand what you expected in Table 1. Could you input manually and explain why?
Book1 (1).xlsx
ABCDEF
1
2DISTRICTPARKING SLOT #VINMAKEMODEL
34TH BN120JH4KA3170LC006787Chevrolet2.6
41ST BN11JS1RF16C442100152Ford 1.1Type III
54TH BN122C4GM68475R667819DodgeORV
62ND BN651P4GH44R0RX359386Chevrolet2.1
72ND BN121P4GH44R0RX359386ToyotaORV II
83RD BN11GBJ7D1B4BV132373Ford 1.0Type III
93RD BN66JH4KA2640GC004861ToyotaORV II
102ND BN41JH4CC2640NC004693ToyotaORV II
11
12
13NEW FORMULA
14
15TABLE 0
16ChevroletToyotaDodgeFord 1.0Ford 1.1 Type III
171ST BN00001
182ND BN12000
193RD BN01010
204TH BN10100
21
22NEW EXAPMPLE
23
24
25TABLE 1
26TYPES OF FORDS
271ST BN2ND BN3RD BN4TH BN
281
29Type III
30
31TABLE 2
32OTHER VEHICLES
331ST BN2ND BN3RD BN4TH BN
34Chevrolet0101
35Toyota0210
36Dodge0001
Sheet3
Cell Formulas
RangeFormula
B17:F20B17=SUMPRODUCT(($A$3:$A$10=$A17)*ISNUMBER(SEARCH($D$3:$D$10,B$16))*ISNUMBER(SEARCH($E$3:$E$10,B$16)))+COUNTIFS($A$3:$A$10,$A17,$D$3:$D$10,B$16)
B34:E36B34=COUNTIFS($A$3:$A$10,B$33,$D$3:$D$10,$A34)
 
Upvote 0
Thanks for the reply Bebo021999! This tracker is just a small example of the main one I have that is several hundreds of lines long so inputting quantities manually would be strenuous. However to explain what I am trying to do is to easy. I am essential taking inventory of these vehicles based on their location and make and models. If you look at the Vehicle List and in the District column I have drops downs (you don't see them here) to select what District a vehicle belongs to. Vehicles tend to get moved around all the time and transferred between districts. If I change a district I want a live update like you see in Table Zero when a District is changed. Now you can see if I have several hundreds of lines of these vehicles how this could be harder to do manually. Table Zero is the original design that works fine. However, upper management says they would like to see it like you see in Tables 1 & 2.

The 2 formulas I used to get this done is below.

Table Zero B17
=IF(AND(B$16<>"",$A17<>""),SUMPRODUCT((SUBSTITUTE($A$3:$A$10,"ST BN","")=TRIM(SUBSTITUTE($A17,"ST BN","")))*($D$3:$D$10=B$16)),"")

Table Zero F17 (Thanks again Fluff for the help on his formula)
=IF(AND(F$16<>"",$A17<>""),SUMPRODUCT((SUBSTITUTE($A$3:$A$10,"ST BN","")=TRIM(SUBSTITUTE($A17,"ST BN","")))*($D$3:$D$10="ford 1.1")*($E$3:$E$10=TRIM(SUBSTITUTE($F16,"Ford 1.1","")))),"")

In the new example you can see I made the changes where the district in now in a row rather than a column and vice versa for the vehicles makes or models. I was able to alter the original formula in to work in Table 2.

Table 2 B34
=IF(AND(A$34<>"",$B33<>""),SUMPRODUCT((SUBSTITUTE($A$3:$A$10,"ST BN","")=TRIM(SUBSTITUTE($B33,"ST BN","")))*($D$3:$D$10=A$34)),"")

As you can see when comparing the formulas all I needed to do was change cells around.

Now in Table 1 is where things got complicated for me. I tried to change my cells around like I did before and then change my =Trim(Substitute parameters around but I couldn't get it to work for me at all. Note: The vehicles model must stay Type III and 1.0.

Table 1 B28
=IF(AND(A$28<>"",$B27<>""),SUMPRODUCT((SUBSTITUTE($A$3:$A$10,"ST BN","")=TRIM(SUBSTITUTE($B27,"ST BN","")))*($D$3:$D$10="ford")*($E$3:$E$10=TRIM(SUBSTITUTE($A28,"Ford","")))),"")

I am reposting my example spreadsheet below because I just noticed the Formula was not in F17 like I thought. Not sure what happen there. I hope this explain more in detail what I am trying to do. Please let me know if you have any questions.

Vehicle Inventory 6.xlsx
ABCDEFG
1
2DISTRICTPARKING SLOT #VINMAKEMODEL
34TH BN120JH4KA3170LC006787Chevrolet2.6
41ST BN11JS1RF16C442100152Ford 1.1Type III
54TH BN122C4GM68475R667819DodgeORV
62ND BN651P4GH44R0RX359386Chevrolet2.1
72ND BN121P4GH44R0RX359386ToyotaORV II
83RD BN11GBJ7D1B4BV132373Ford 1.0Type III
93RD BN66JH4KA2640GC004861ToyotaORV II
102ND BN41JH4CC2640NC004693ToyotaORV II
11
12ORIGINAL EXAMPLE
13
14
15TABLE 0
16ChevroletToyotaDodgeFord 1.0Ford 1.1 Type III
171ST BN00001
182ND BN12000
193RD BN01010
204TH BN10100
21
22NEW EXAPMPLE
23
24
25TABLE 1
26TYPES OF FORDS
271ST BN2ND BN3RD BN4TH BN
281.00
29Type III
30
31TABLE 2
32OTHER VEHICLES
331ST BN2ND BN3RD BN4TH BN
34Chevrolet0101
35Toyota0210
36Dodge0001
37
Sheet1
Cell Formulas
RangeFormula
B17:E17B17=IF(AND(B$16<>"",$A17<>""),SUMPRODUCT((SUBSTITUTE($A$3:$A$10,"ST BN","")=TRIM(SUBSTITUTE($A17,"ST BN","")))*($D$3:$D$10=B$16)),"")
F17F17=IF(AND(F$16<>"",$A17<>""),SUMPRODUCT((SUBSTITUTE($A$3:$A$10,"ST BN","")=TRIM(SUBSTITUTE($A17,"ST BN","")))*($D$3:$D$10="ford 1.1")*($E$3:$E$10=TRIM(SUBSTITUTE($F16,"Ford 1.1","")))),"")
B18:E18B18=IF(AND(B$16<>"",$A18<>""),SUMPRODUCT((SUBSTITUTE($A$3:$A$10,"ND BN","")=TRIM(SUBSTITUTE($A18,"ND BN","")))*($D$3:$D$10=B$16)),"")
F18F18=IF(AND(F$16<>"",$A18<>""),SUMPRODUCT((SUBSTITUTE($A$3:$A$10,"ND BN","")=TRIM(SUBSTITUTE($A18,"ND BN","")))*($D$3:$D$10="ford 1.1")*($E$3:$E$10=TRIM(SUBSTITUTE($F16,"Ford 1.1","")))),"")
B19:E19B19=IF(AND(B$16<>"",$A19<>""),SUMPRODUCT((SUBSTITUTE($A$3:$A$10,"RD BN","")=TRIM(SUBSTITUTE($A19,"RD BN","")))*($D$3:$D$10=B$16)),"")
F19F19=IF(AND(F$16<>"",$A19<>""),SUMPRODUCT((SUBSTITUTE($A$3:$A$10,"RD BN","")=TRIM(SUBSTITUTE($A19,"RD BN","")))*($D$3:$D$10="ford 1.1")*($E$3:$E$10=TRIM(SUBSTITUTE($F16,"Ford 1.1","")))),"")
B20:E20B20=IF(AND(B$16<>"",$A20<>""),SUMPRODUCT((SUBSTITUTE($A$3:$A$10,"TH BN","")=TRIM(SUBSTITUTE($A20,"TH BN","")))*($D$3:$D$10=B$16)),"")
F20F20=IF(AND(F$16<>"",$A20<>""),SUMPRODUCT((SUBSTITUTE($A$3:$A$10,"TH BN","")=TRIM(SUBSTITUTE($A20,"TH BN","")))*($D$3:$D$10="ford 1.1")*($E$3:$E$10=TRIM(SUBSTITUTE($F16,"Ford 1.1","")))),"")
B28B28=IF(AND(A$28<>"",$B27<>""),SUMPRODUCT((SUBSTITUTE($A$3:$A$10,"ST BN","")=TRIM(SUBSTITUTE($B27,"ST BN","")))*($D$3:$D$10="ford")*($E$3:$E$10=TRIM(SUBSTITUTE($A28,"Ford","")))),"")
B34B34=IF(AND(A$34<>"",$B33<>""),SUMPRODUCT((SUBSTITUTE($A$3:$A$10,"ST BN","")=TRIM(SUBSTITUTE($B33,"ST BN","")))*($D$3:$D$10=A$34)),"")
C34C34=IF(AND(A$34<>"",$C33<>""),SUMPRODUCT((SUBSTITUTE($A$3:$A$10,"ND BN","")=TRIM(SUBSTITUTE($C33,"ND BN","")))*($D$3:$D$10=A$34)),"")
D34D34=IF(AND(A$34<>"",$D33<>""),SUMPRODUCT((SUBSTITUTE($A$3:$A$10,"RD BN","")=TRIM(SUBSTITUTE($D33,"RD BN","")))*($D$3:$D$10=A$34)),"")
E34E34=IF(AND(A$34<>"",$E33<>""),SUMPRODUCT((SUBSTITUTE($A$3:$A$10,"ND BN","")=TRIM(SUBSTITUTE($E33,"ND BN","")))*($D$3:$D$10=A$34)),"")
B35B35=IF(AND(A$35<>"",$B33<>""),SUMPRODUCT((SUBSTITUTE($A$3:$A$10,"ST BN","")=TRIM(SUBSTITUTE($B33,"ST BN","")))*($D$3:$D$10=A$35)),"")
C35C35=IF(AND(A$35<>"",$C33<>""),SUMPRODUCT((SUBSTITUTE($A$3:$A$10,"ND BN","")=TRIM(SUBSTITUTE($C33,"ND BN","")))*($D$3:$D$10=A$35)),"")
D35D35=IF(AND(A$35<>"",$D33<>""),SUMPRODUCT((SUBSTITUTE($A$3:$A$10,"RD BN","")=TRIM(SUBSTITUTE($D33,"RD BN","")))*($D$3:$D$10=A$35)),"")
E35E35=IF(AND(A$35<>"",$E33<>""),SUMPRODUCT((SUBSTITUTE($A$3:$A$10,"TH BN","")=TRIM(SUBSTITUTE($E33,"TH BN","")))*($D$3:$D$10=A$35)),"")
B36B36=IF(AND(A$36<>"",$B33<>""),SUMPRODUCT((SUBSTITUTE($A$3:$A$10,"ST BN","")=TRIM(SUBSTITUTE($B33,"ST BN","")))*($D$3:$D$10=A$36)),"")
C36C36=IF(AND(A$36<>"",$C33<>""),SUMPRODUCT((SUBSTITUTE($A$3:$A$10,"ND BN","")=TRIM(SUBSTITUTE($C33,"ND BN","")))*($D$3:$D$10=A$36)),"")
D36D36=IF(AND(A$36<>"",$D33<>""),SUMPRODUCT((SUBSTITUTE($A$3:$A$10,"RD BN","")=TRIM(SUBSTITUTE($D33,"RD BN","")))*($D$3:$D$10=A$36)),"")
E36E36=IF(AND(A$36<>"",$E33<>""),SUMPRODUCT((SUBSTITUTE($A$3:$A$10,"ND BN","")=TRIM(SUBSTITUTE($E33,"ND BN","")))*($D$3:$D$10=A$36)),"")
Cells with Data Validation
CellAllowCriteria
A3:A10List--, 1ST BN, 2ND BN, 3RD BN, 4TH BN,
A17:A20List--, 1ST BN, 2ND BN, 3RD BN, 4TH BN,
B27:E27List--, 1ST BN, 2ND BN, 3RD BN, 4TH BN,
B33:E33List--, 1ST BN, 2ND BN, 3RD BN, 4TH BN,
 
Upvote 0
Hi, see the linked file for a possible solution...

The formulas used in the table...
B17: =IF(AND(B$16<>"",$A17<>""),SUMPRODUCT(($A$3:$A$10=$A17)*($D$3:$D$10=B$16)),"") (Range: B17:E20)
F17: =IF(AND(F$16<>"",$A17<>""),SUMPRODUCT(($A$3:$A$10=$A17)*($D$3:$D$10="Ford 1.1")*($E$3:$E$10="Type III")),"") (Range: F17:F20)
B28: =IF(AND($A28<>"",B$27<>""),SUMPRODUCT(($A$3:$A$10=B$27)*($D$3:$D$10="Ford 1.0")),"") (Range: B28:E28)
B29: =IF(AND($A29<>"",B$27<>""),SUMPRODUCT(($A$3:$A$10=B$27)*($D$3:$D$10="Ford 1.1")*($E$3:$E$10="Type III")),"") (Range: B29:E29)
B34: =IF(AND($A34<>"",B$33<>""),SUMPRODUCT(($A$3:$A$10=B$33)*($D$3:$D$10=$A34)),"") (Range: B34:E36)

VehicleInventory.xlsx

VehicleInventory1.png


VehicleInventory2.png
 
Upvote 1
Solution
Works perfectly! The formula is definitely not as complicated as well. Thank you Fjns!
 
Upvote 0
Hi, I am very glad that the formulas work.
If I have enough knowledge, I am happy to help at any time.
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Question Question
Clinching NHL Playoff Spot
Replies
2
Views
74
dotcanada
D
J
  • Solved
Having issues with Formula!!!
2
Replies
10
Views
746
ExceLoki
ExceLoki
C
  • Question Question
Only Brings Unique Value Across
Replies
3
Views
107
kvsrinivasamurthy
K
C
  • Solved
Unable to copy formula down sheet if data isn't there.
Replies
3
Views
163
Fluff
Fluff
J
  • Question Question
Sum only when time overlaps
Replies
1
Views
282
awoohaw
awoohaw

Forum statistics

Threads
1,224,823
Messages
6,181,178
Members
453,021
Latest member
Justyna P

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