Formula issue

[Steved]

Hello from Steved

Below is entered as an array Formula.

The Below is giving me a 0 value should be a 3 value.

What is required please.

=SUM(IF($F$2:$F$5000="1-City",IF(E2:E5000="Mk 2
HEV",G$2:G$5000,0),0))

Thankyou.

 

[Biff]

Hi steved!

Try this:

=SUMPRODUCT(--(F2:F5000="1-City"),--(E2:E5000="Mk 2
HEV"),G2:G5000)

Biff

 

[Steved]

Hello Biff from Steved

It still gives me a 0 Value

I've tried below but gives me #VALUE!

any further thoughts

=SUMPRODUCT(--(F2:F5000),--(E2:E5000),G2:G5000)

 

[Steved]

Hello Biff from Steved

It still gives me a 0 Value

I've tried below but gives me #VALUE!

any further thoughts

=SUMPRODUCT(--(F2:F5000),--(E2:E5000),G2:G5000)

 

[Biff]

Hi Steved!

That 2nd criteria, "Mk 2 HEV", looks like it may be the
problem in that there may be additional spaces in the
range E2:E5000.

Try adding this to the second array argument:

......),--(TRIM(E2:E5000)="Mk 2 HEV"),.......

Biff

 

[Steved]

Hello from Steved

=SUMPRODUCT(--(F2:F5000="1-City"),--(TRIM(E2:E5000="Mk 2
HEV"),G2:G5000))

The above is not giving a value.

Id it possible to write a formula that will use Countif
statement Please

Thankyou.

 

[Dave Peterson]

You moved a closing paren.

=SUMPRODUCT(--(F2:F5000="1-City"),
--(TRIM(E2:E5000)="Mk 2 HEV",G2:G5000))

Still one cell

 

[Biff]

Hi Steved!

There's more than likely a "problem" with the criteria in
either the ranges or the hard coded text used in the
formula!

You can try this formula but it is essentially the same as
a sumproduct: entered as an array

=SUM((F2:F5000="1-city")*(E2:E5000="Mk 2 hev")*(G2:G5000))

*Wait a minute!*

What *EXACTLY* are you trying to do? Are you trying a
conditional *COUNT*? Or, a conditional *SUM*?

Why are your formulas always so complicated? <vbg>

Biff

 

[Steved]

Thanks Dave Yes it is now doing what it is supposed to do.

Cheers.

 

[Steved]

Hello Biff from Steved

Your new formula also works and I thankyou.

Cheers