sumproduct

[Chas17]

=SUMPRODUCT((C4:C12=1)*(D4:D12="AOV")*(E4:E12>""))

If colmn C contained more than just a 1, e.g. 1JCHBUV255, how could the above formula be adjusted to use only the first char in colm c?

Thanks,
Chas

 

[just_jon]

Try --

=SUMPRODUCT(--(C4:C12=1),--(LEFT(D4:D12,3)="AOV"),--(E4:E12>""))

 

[Aladin Akyurek]

=SUMPRODUCT((C4:C12=1)*(D4:D12="AOV")*(E4:E12>""))

If colmn C contained more than just a 1, e.g. 1JCHBUV255, how could the above formula be adjusted to use only the first char in colm c?...

You mean everything that starts with 1?

And, What does the conditional

(E4:E12>"")

mean?

 

[Chas17]

Just_Jon,
Your formulas works, I changed it to

=SUMPRODUCT(--(LEFT(C4:C12)="1"),--(D4:D12="AOV"),--(E4:E12>""))

colm C is what I needed.

Aladin,
Colm C contains str's such as
1JCHBUV255
1JSIAV44
2JCHBUV531
3JSIBUV668

The first char is the UNIT, search for Unit 1, needed to look for all in col c that first char was a 1.

the conditional (E4:E12>"") was looking for if a string had been entered in those cells or if it was still blank. colm E would have a User name entered if that work was complete. The Search was for Unit 1 AOV type valves where work was indicated completed by user name entry.

Incidently, in the formula
=SUMPRODUCT(--(LEFT(C4:C12)="1"),--(D4:D12="AOV"),--(E4:E12>""))
what do the -- mean?
how does it differ from
=SUMPRODUCT((C4:C12=1)*(D4:D12="AOV")*(E4:E12>""))
Thanks,
Chas

 

[Aladin Akyurek]

...

Incidently, in the formula

=SUMPRODUCT(--(LEFT(C4:C12)="1"),--(D4:D12="AOV"),--(E4:E12>""))

what do the -- mean?

Conditional upon evaluation yield arrays of TRUE's and/or FALSE's. The -- bit converts them into 1's and/or 0's, an array of numbers as SumProduct requires.

how does it differ from

=SUMPRODUCT((C4:C12=1)*(D4:D12="AOV")*(E4:E12>""))

The former has 3 arrays, the latter effectively 1. Conversion from logical values to numbers occur in the latter as a side-effect of multiplication.

Recall that the syntax of SumProduct is...

SUMPRODUCT(array1,array2,...)

The former is thus closer to this. And, the -- bit has a more robust temporal profile, so the former is slightly faster than the latter.

 

[just_jon]

My apologies for not having bothered to actually read your clearly-stated post before responding.

 

[Chas17]

Aladin,
Thank you for the reply and taking the time to teach a little. If I get back to the Netherlands some day I'd like to attend one of your lectures!
Thanks so much,
Chas

 

[Chas17]

Just_Jon,
No, Thanks. Formula worked and I learned more having to manipulate it.
Hands on stuff. Thanks.
Chas

 

[just_jon]

Just_Jon,
No, Thanks. Formula worked and I learned more having to manipulate it.
Hands on stuff. Thanks.
Chas

Well! I'll just have to give Bad Advice more often, then!