Unable to get formula to work correctly :(

JB2385 · Apr 27, 2023

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

A

B

C

D

E

F

G

1

2

DISTRICT

PARKING SLOT #

VIN

MAKE

MODEL

3

4TH BN

120

JH4KA3170LC006787

Chevrolet

2.6

4

1ST BN

11

JS1RF16C442100152

Ford 1.1

Type III

5

4TH BN

12

2C4GM68475R667819

Dodge

ORV

6

2ND BN

65

1P4GH44R0RX359386

Chevrolet

2.1

7

2ND BN

12

1P4GH44R0RX359386

Toyota

ORV II

8

3RD BN

1

1GBJ7D1B4BV132373

Ford 1.0

Type III

9

3RD BN

66

JH4KA2640GC004861

Toyota

ORV II

10

2ND BN

41

JH4CC2640NC004693

Toyota

ORV II

11

12

ORIGINAL EXAMPLE

13

14

15

TABLE 0

16

Chevrolet

Toyota

Dodge

Ford 1.0

Ford 1.1 Type III

17

1ST BN

0

18

2ND BN

1

2

0

19

3RD BN

20

4TH BN

21

22

NEW EXAPMPLE

23

24

25

TABLE 1

26

TYPES OF FORDS

27

1ST BN

2ND BN

3RD BN

4TH BN

28

1.0

0

29

Type III

30

31

TABLE 2

32

OTHER VEHICLES

33

1ST BN

2ND BN

3RD BN

4TH BN

34

Chevrolet

35

Toyota

36

Dodge

0

1

37

Sheet1

Cell Formulas
Range	Formula
B17:E17	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)),"")
F17	F17	=B263
B18:E18	B18	=IF(AND(B$16<>"",$A18<>""),SUMPRODUCT((SUBSTITUTE($A$3:$A$10,"ND BN","")=TRIM(SUBSTITUTE($A18,"ND BN","")))*($D$3:$D$10=B$16)),"")
F18	F18	=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:E19	B19	=IF(AND(B$16<>"",$A19<>""),SUMPRODUCT((SUBSTITUTE($A$3:$A$10,"RD BN","")=TRIM(SUBSTITUTE($A19,"RD BN","")))*($D$3:$D$10=B$16)),"")
F19	F19	=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:E20	B20	=IF(AND(B$16<>"",$A20<>""),SUMPRODUCT((SUBSTITUTE($A$3:$A$10,"TH BN","")=TRIM(SUBSTITUTE($A20,"TH BN","")))*($D$3:$D$10=B$16)),"")
F20	F20	=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","")))),"")
B28	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","")))),"")
B34	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)),"")
C34	C34	=IF(AND(A$34<>"",$C33<>""),SUMPRODUCT((SUBSTITUTE($A$3:$A$10,"ND BN","")=TRIM(SUBSTITUTE($C33,"ND BN","")))*($D$3:$D$10=A$34)),"")
D34	D34	=IF(AND(A$34<>"",$D33<>""),SUMPRODUCT((SUBSTITUTE($A$3:$A$10,"RD BN","")=TRIM(SUBSTITUTE($D33,"RD BN","")))*($D$3:$D$10=A$34)),"")
E34	E34	=IF(AND(A$34<>"",$E33<>""),SUMPRODUCT((SUBSTITUTE($A$3:$A$10,"ND BN","")=TRIM(SUBSTITUTE($E33,"ND BN","")))*($D$3:$D$10=A$34)),"")
B35	B35	=IF(AND(A$35<>"",$B33<>""),SUMPRODUCT((SUBSTITUTE($A$3:$A$10,"ST BN","")=TRIM(SUBSTITUTE($B33,"ST BN","")))*($D$3:$D$10=A$35)),"")
C35	C35	=IF(AND(A$35<>"",$C33<>""),SUMPRODUCT((SUBSTITUTE($A$3:$A$10,"ND BN","")=TRIM(SUBSTITUTE($C33,"ND BN","")))*($D$3:$D$10=A$35)),"")
D35	D35	=IF(AND(A$35<>"",$D33<>""),SUMPRODUCT((SUBSTITUTE($A$3:$A$10,"RD BN","")=TRIM(SUBSTITUTE($D33,"RD BN","")))*($D$3:$D$10=A$35)),"")
E35	E35	=IF(AND(A$35<>"",$E33<>""),SUMPRODUCT((SUBSTITUTE($A$3:$A$10,"TH BN","")=TRIM(SUBSTITUTE($E33,"TH BN","")))*($D$3:$D$10=A$35)),"")
B36	B36	=IF(AND(A$36<>"",$B33<>""),SUMPRODUCT((SUBSTITUTE($A$3:$A$10,"ST BN","")=TRIM(SUBSTITUTE($B33,"ST BN","")))*($D$3:$D$10=A$36)),"")
C36	C36	=IF(AND(A$36<>"",$C33<>""),SUMPRODUCT((SUBSTITUTE($A$3:$A$10,"ND BN","")=TRIM(SUBSTITUTE($C33,"ND BN","")))*($D$3:$D$10=A$36)),"")
D36	D36	=IF(AND(A$36<>"",$D33<>""),SUMPRODUCT((SUBSTITUTE($A$3:$A$10,"RD BN","")=TRIM(SUBSTITUTE($D33,"RD BN","")))*($D$3:$D$10=A$36)),"")
E36	E36	=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
Cell	Allow	Criteria
A3:A10	List	--, 1ST BN, 2ND BN, 3RD BN, 4TH BN,
A17:A20	List	--, 1ST BN, 2ND BN, 3RD BN, 4TH BN,
B27:E27	List	--, 1ST BN, 2ND BN, 3RD BN, 4TH BN,
B33:E33	List	--, 1ST BN, 2ND BN, 3RD BN, 4TH BN,

bebo021999 · Apr 27, 2023

I do not understand what you expected in Table 1. Could you input manually and explain why?

Book1 (1).xlsx

A

B

C

D

E

F

1

2

DISTRICT

PARKING SLOT #

VIN

MAKE

MODEL

3

4TH BN

120

JH4KA3170LC006787

Chevrolet

2.6

4

1ST BN

11

JS1RF16C442100152

Ford 1.1

Type III

5

4TH BN

12

2C4GM68475R667819

Dodge

ORV

6

2ND BN

65

1P4GH44R0RX359386

Chevrolet

2.1

7

2ND BN

12

1P4GH44R0RX359386

Toyota

ORV II

8

3RD BN

1

1GBJ7D1B4BV132373

Ford 1.0

Type III

9

3RD BN

66

JH4KA2640GC004861

Toyota

ORV II

10

2ND BN

41

JH4CC2640NC004693

Toyota

ORV II

11

12

13

NEW FORMULA

14

15

TABLE 0

16

Chevrolet

Toyota

Dodge

Ford 1.0

Ford 1.1 Type III

17

1ST BN

0

1

18

2ND BN

1

2

0

19

3RD BN

20

4TH BN

21

22

NEW EXAPMPLE

23

24

25

TABLE 1

26

TYPES OF FORDS

27

1ST BN

2ND BN

3RD BN

4TH BN

28

1

29

Type III

30

31

TABLE 2

32

OTHER VEHICLES

33

1ST BN

2ND BN

3RD BN

4TH BN

34

Chevrolet

35

Toyota

36

Dodge

0

1

Sheet3

Cell Formulas
Range	Formula
B17:F20	B17	=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:E36	B34	=COUNTIFS($A$3:$A$10,B$33,$D$3:$D$10,$A34)

JB2385 · Apr 28, 2023

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

A

B

C

D

E

F

G

1

2

DISTRICT

PARKING SLOT #

VIN

MAKE

MODEL

3

4TH BN

120

JH4KA3170LC006787

Chevrolet

2.6

4

1ST BN

11

JS1RF16C442100152

Ford 1.1

Type III

5

4TH BN

12

2C4GM68475R667819

Dodge

ORV

6

2ND BN

65

1P4GH44R0RX359386

Chevrolet

2.1

7

2ND BN

12

1P4GH44R0RX359386

Toyota

ORV II

8

3RD BN

1

1GBJ7D1B4BV132373

Ford 1.0

Type III

9

3RD BN

66

JH4KA2640GC004861

Toyota

ORV II

10

2ND BN

41

JH4CC2640NC004693

Toyota

ORV II

11

12

ORIGINAL EXAMPLE

13

14

15

TABLE 0

16

Chevrolet

Toyota

Dodge

Ford 1.0

Ford 1.1 Type III

17

1ST BN

0

1

18

2ND BN

1

2

0

19

3RD BN

20

4TH BN

21

22

NEW EXAPMPLE

23

24

25

TABLE 1

26

TYPES OF FORDS

27

1ST BN

2ND BN

3RD BN

4TH BN

28

1.0

0

29

Type III

30

31

TABLE 2

32

OTHER VEHICLES

33

1ST BN

2ND BN

3RD BN

4TH BN

34

Chevrolet

35

Toyota

36

Dodge

0

1

37

Sheet1

Cell Formulas
Range	Formula
B17:E17	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)),"")
F17	F17	=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:E18	B18	=IF(AND(B$16<>"",$A18<>""),SUMPRODUCT((SUBSTITUTE($A$3:$A$10,"ND BN","")=TRIM(SUBSTITUTE($A18,"ND BN","")))*($D$3:$D$10=B$16)),"")
F18	F18	=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:E19	B19	=IF(AND(B$16<>"",$A19<>""),SUMPRODUCT((SUBSTITUTE($A$3:$A$10,"RD BN","")=TRIM(SUBSTITUTE($A19,"RD BN","")))*($D$3:$D$10=B$16)),"")
F19	F19	=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:E20	B20	=IF(AND(B$16<>"",$A20<>""),SUMPRODUCT((SUBSTITUTE($A$3:$A$10,"TH BN","")=TRIM(SUBSTITUTE($A20,"TH BN","")))*($D$3:$D$10=B$16)),"")
F20	F20	=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","")))),"")
B28	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","")))),"")
B34	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)),"")
C34	C34	=IF(AND(A$34<>"",$C33<>""),SUMPRODUCT((SUBSTITUTE($A$3:$A$10,"ND BN","")=TRIM(SUBSTITUTE($C33,"ND BN","")))*($D$3:$D$10=A$34)),"")
D34	D34	=IF(AND(A$34<>"",$D33<>""),SUMPRODUCT((SUBSTITUTE($A$3:$A$10,"RD BN","")=TRIM(SUBSTITUTE($D33,"RD BN","")))*($D$3:$D$10=A$34)),"")
E34	E34	=IF(AND(A$34<>"",$E33<>""),SUMPRODUCT((SUBSTITUTE($A$3:$A$10,"ND BN","")=TRIM(SUBSTITUTE($E33,"ND BN","")))*($D$3:$D$10=A$34)),"")
B35	B35	=IF(AND(A$35<>"",$B33<>""),SUMPRODUCT((SUBSTITUTE($A$3:$A$10,"ST BN","")=TRIM(SUBSTITUTE($B33,"ST BN","")))*($D$3:$D$10=A$35)),"")
C35	C35	=IF(AND(A$35<>"",$C33<>""),SUMPRODUCT((SUBSTITUTE($A$3:$A$10,"ND BN","")=TRIM(SUBSTITUTE($C33,"ND BN","")))*($D$3:$D$10=A$35)),"")
D35	D35	=IF(AND(A$35<>"",$D33<>""),SUMPRODUCT((SUBSTITUTE($A$3:$A$10,"RD BN","")=TRIM(SUBSTITUTE($D33,"RD BN","")))*($D$3:$D$10=A$35)),"")
E35	E35	=IF(AND(A$35<>"",$E33<>""),SUMPRODUCT((SUBSTITUTE($A$3:$A$10,"TH BN","")=TRIM(SUBSTITUTE($E33,"TH BN","")))*($D$3:$D$10=A$35)),"")
B36	B36	=IF(AND(A$36<>"",$B33<>""),SUMPRODUCT((SUBSTITUTE($A$3:$A$10,"ST BN","")=TRIM(SUBSTITUTE($B33,"ST BN","")))*($D$3:$D$10=A$36)),"")
C36	C36	=IF(AND(A$36<>"",$C33<>""),SUMPRODUCT((SUBSTITUTE($A$3:$A$10,"ND BN","")=TRIM(SUBSTITUTE($C33,"ND BN","")))*($D$3:$D$10=A$36)),"")
D36	D36	=IF(AND(A$36<>"",$D33<>""),SUMPRODUCT((SUBSTITUTE($A$3:$A$10,"RD BN","")=TRIM(SUBSTITUTE($D33,"RD BN","")))*($D$3:$D$10=A$36)),"")
E36	E36	=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
Cell	Allow	Criteria
A3:A10	List	--, 1ST BN, 2ND BN, 3RD BN, 4TH BN,
A17:A20	List	--, 1ST BN, 2ND BN, 3RD BN, 4TH BN,
B27:E27	List	--, 1ST BN, 2ND BN, 3RD BN, 4TH BN,
B33:E33	List	--, 1ST BN, 2ND BN, 3RD BN, 4TH BN,

fjns · Apr 28, 2023

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

JB2385 · Apr 28, 2023

Works perfectly! The formula is definitely not as complicated as well. Thank you Fjns!

fjns · Apr 28, 2023

Hi, I am very glad that the formulas work.
If I have enough knowledge, I am happy to help at any time.

Unable to get formula to work correctly :(

JB2385

New Member

Excel Facts

bebo021999

Well-known Member

JB2385

New Member

fjns

Board Regular

JB2385

New Member

fjns

Board Regular

Similar threads

Forum statistics

Share this page

Unable to get formula to work correctly :(

JB2385

New Member

Excel Facts

bebo021999

Well-known Member

JB2385

New Member

fjns

Board Regular

JB2385

New Member

fjns

Board Regular

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock