Having issues with Formula!!!

JB2385 · Apr 26, 2023

I am going to break down how I am understanding a portion of this formula below thus will lead to my question.

Formula:

=IF(AND(B$13<>"",$A14<>""),SUMPRODUCT((SUBSTITUTE($A$3:$A$10,"1/","")=TRIM(SUBSTITUTE($A14,"Site","")))*($D$3:$D$10=B$13)),"")

The portions of the formula =TRIM(SUBSTITUTE($A14,"Site","") is essential taking out "Site" from the cell and leaving the Site number and in this case its reading it as 1 instead of Site 1. Please correct me if I am wrong.

My question is how can I had a second =Trim(substitute like above or do I not need it? My assumption would be like this (below). Again correct me if I am wrong.

=IF(AND(F$13<>"",$A14<>""),SUMPRODUCT((SUBSTITUTE($A$3:$A$10,"/5","")=TRIM(SUBSTITUTE($A14,"Site","")=TRIM(SUBSTITUTE($F13,"Ford 1.1","")))*($E$3:$E$10=F$13)),""))

In my master tracker I have another substitution I need input in F14 and the example above is how I was thinking it would be implemented however, I am receiving #VALUE! in the cell F14 instead of the number. The "Ford 1.1" is what I am trying to substitute so the formula will only count Type III instead of "Ford 1.1". I am using the first formula in all my other cells except column F. I hope this was descriptive enough. Please let me know if there any questions.

Fluff · Apr 26, 2023

Maybe

Excel Formula:

=IF(AND(F$13<>"",$A14<>""),SUMPRODUCT((SUBSTITUTE($A$3:$A$10,"/5","")=TRIM(SUBSTITUTE($A14,"Site",""))*($E$3:$E$10=TRIM(SUBSTITUTE($F13,"Ford 1.1","")))),""))

JB2385 · Apr 26, 2023

Fluff said:

Maybe

Excel Formula:

=IF(AND(F$13<>"",$A14<>""),SUMPRODUCT((SUBSTITUTE($A$3:$A$10,"/5","")=TRIM(SUBSTITUTE($A14,"Site",""))*($E$3:$E$10=TRIM(SUBSTITUTE($F13,"Ford 1.1","")))),""))

Thank you for the quick reply. It gave me the same #Value! error.

Fluff · Apr 26, 2023

In that case can you post your data.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

JB2385 · Apr 26, 2023

Fluff said:
In that case can you post your data.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

This is what I am working with.

Vehicle Inventory 4.xlsx

A

B

C

D

E

F

1

2

DISTRICT / SITE

PARKING SLOT #

VIN

MAKE

MODEL

3

1/1

120

JH4KA3170LC006787

Chevrolet

2.6

4

1/1

11

JS1RF16C442100152

Ford 1.1

Type III

5

1/1

12

2C4GM68475R667819

Dodge

ORV

6

1/1

65

1P4GH44R0RX359386

Chevrolet

2.1

7

1/1

12

1P4GH44R0RX359386

Toyota

ORV II

8

1/1

1

1GBJ7D1B4BV132373

Ford 1.0

Type III

9

1/4

66

JH4KA2640GC004861

Toyota

ORV II

10

1/1

41

JH4CC2640NC004693

Toyota

ORV II

11

12

13

Chevrolet

Toyota

Dodge

Ford 1.0

Ford 1.1 Type III

14

Site 1

2

1

#VALUE!

15

Site 2

0

16

Site 3

0

17

Site 4

0

1

0

Sheet1

Cell Formulas
Range	Formula
B14:E17	B14	=IF(AND(B$13<>"",$A14<>""),SUMPRODUCT((SUBSTITUTE($A$3:$A$10,"1/","")=TRIM(SUBSTITUTE($A14,"Site","")))*($D$3:$D$10=B$13)),"")
F14	F14	=IF(AND(F$13<>"",$A14<>""),SUMPRODUCT((SUBSTITUTE($A$3:$A$10,"/5","")=TRIM(SUBSTITUTE($A14,"Site",""))*($E$3:$E$10=TRIM(SUBSTITUTE($F13,"Ford 1.1","")))),""))

Fluff · Apr 26, 2023

Thanks for that, how about

Fluff.xlsm

A

B

C

D

E

F

1

2

DISTRICT / SITE

PARKING SLOT #

VIN

MAKE

MODEL

3

1/1

120

JH4KA3170LC006787

Chevrolet

2.6

4

1/1

11

JS1RF16C442100152

Ford 1.1

Type III

5

1/1

12

2C4GM68475R667819

Dodge

ORV

6

1/1

65

1P4GH44R0RX359386

Chevrolet

2.1

7

1/1

12

1P4GH44R0RX359386

Toyota

ORV II

8

1/1

1

1GBJ7D1B4BV132373

Ford 1.0

Type III

9

1/4

66

JH4KA2640GC004861

Toyota

ORV II

10

1/1

41

JH4CC2640NC004693

Toyota

ORV II

11

12

13

Chevrolet

Toyota

Dodge

Ford 1.0

Ford 1.1 Type III

14

Site 1

2

1

2

15

Site 2

0

16

Site 3

0

17

Site 4

0

1

0

Dashboard

Cell Formulas
Range	Formula
B14:E17	B14	=IF(AND(B$13<>"",$A14<>""),SUMPRODUCT((SUBSTITUTE($A$3:$A$10,"1/","")=TRIM(SUBSTITUTE($A14,"Site","")))*($D$3:$D$10=B$13)),"")
F14	F14	=IF(AND(F$13<>"",$A14<>""),SUMPRODUCT((SUBSTITUTE($A$3:$A$10,"1/","")=TRIM(SUBSTITUTE($A14,"Site","")))*($E$3:$E$10=TRIM(SUBSTITUTE($F13,"Ford 1.1","")))),"")
F15:F17	F15	=IF(AND(F$13<>"",$A15<>""),SUMPRODUCT((SUBSTITUTE($A$3:$A$10,"/5","")=TRIM(SUBSTITUTE($A15,"Site","")))*($E$3:$E$10=TRIM(SUBSTITUTE($F14,"Ford 1.1","")))),"")

JB2385 · Apr 26, 2023

Fluff said:
Thanks for that, how about

Fluff.xlsm
A B C D E F
1
2 DISTRICT / SITE PARKING SLOT # VIN MAKE MODEL
3 1/1 120 JH4KA3170LC006787 Chevrolet 2.6
4 1/1 11 JS1RF16C442100152 Ford 1.1 Type III
5 1/1 12 2C4GM68475R667819 Dodge ORV
6 1/1 65 1P4GH44R0RX359386 Chevrolet 2.1
7 1/1 12 1P4GH44R0RX359386 Toyota ORV II
8 1/1 1 1GBJ7D1B4BV132373 Ford 1.0 Type III
9 1/4 66 JH4KA2640GC004861 Toyota ORV II
10 1/1 41 JH4CC2640NC004693 Toyota ORV II
11
12
13 Chevrolet Toyota Dodge Ford 1.0 Ford 1.1 Type III
14 Site 1 2 2 1 1 2
15 Site 2 0 0 0 0 0
16 Site 3 0 0 0 0 0
17 Site 4 0 1 0 0 0
Dashboard
Cell Formulas
Range Formula
B14:E17 B14 =IF(AND(B$13<>"",$A14<>""),SUMPRODUCT((SUBSTITUTE($A$3:$A$10,"1/","")=TRIM(SUBSTITUTE($A14,"Site","")))*($D$3:$D$10=B$13)),"")
F14 F14 =IF(AND(F$13<>"",$A14<>""),SUMPRODUCT((SUBSTITUTE($A$3:$A$10,"1/","")=TRIM(SUBSTITUTE($A14,"Site","")))*($E$3:$E$10=TRIM(SUBSTITUTE($F13,"Ford 1.1","")))),"")
F15:F17 F15 =IF(AND(F$13<>"",$A15<>""),SUMPRODUCT((SUBSTITUTE($A$3:$A$10,"/5","")=TRIM(SUBSTITUTE($A15,"Site","")))*($E$3:$E$10=TRIM(SUBSTITUTE($F14,"Ford 1.1","")))),"")

Thank you for the quick response. It looks like its picking up 2 Ford 1.1 Type III for Site 1. There is only 1. I think it is picking up the Ford 1.0 Type III and the Ford 1.1 Type III.

Fluff · Apr 26, 2023

In that case try

Excel Formula:

=IF(AND(F$13<>"",$A14<>""),SUMPRODUCT((SUBSTITUTE($A$3:$A$10,"1/","")=TRIM(SUBSTITUTE($A14,"Site","")))*($D$3:$D$10="ford 1.1")*($E$3:$E$10=TRIM(SUBSTITUTE($F13,"Ford 1.1","")))),"")

JB2385 · Apr 26, 2023

JB2385 said:
Thank you for the quick response. It looks like its picking up 2 Ford 1.1 Type III for Site 1. There is only 1. I think it is picking up the Ford 1.0 Type III and the Ford 1.1 Type III.

Fluff said:

In that case try

Excel Formula:

=IF(AND(F$13<>"",$A14<>""),SUMPRODUCT((SUBSTITUTE($A$3:$A$10,"1/","")=TRIM(SUBSTITUTE($A14,"Site","")))*($D$3:$D$10="ford 1.1")*($E$3:$E$10=TRIM(SUBSTITUTE($F13,"Ford 1.1","")))),"")

Word perfectly!!!! Thank you so much! Issue resolved.

Fluff · Apr 26, 2023

You're welcome & thanks for the feedback.

Having issues with Formula!!!

JB2385

New Member

Attachments

Excel Facts

Fluff

MrExcel MVP, Moderator

JB2385

New Member

Fluff

MrExcel MVP, Moderator

JB2385

New Member

Fluff

MrExcel MVP, Moderator

JB2385

New Member

Fluff

MrExcel MVP, Moderator

JB2385

New Member

Fluff

MrExcel MVP, Moderator

Similar threads

Forum statistics

Share this page

Having issues with Formula!!!

New Member

Attachments

Excel Facts

MrExcel MVP, Moderator

New Member

MrExcel MVP, Moderator

New Member

MrExcel MVP, Moderator

New Member

MrExcel MVP, Moderator

New Member

MrExcel MVP, Moderator

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