# sumproduct

#### Chas17

##### Well-known Member
=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

### Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

#### just_jon

##### Legend
Try --

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

##### MrExcel MVP
Chas17 said:
=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

##### Well-known Member
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.

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

##### MrExcel MVP
Chas17 said:
...

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

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

#### Chas17

##### Well-known Member
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

##### Well-known Member
Just_Jon,
No, Thanks. Formula worked and I learned more having to manipulate it.
Hands on stuff. Thanks.
Chas

#### just_jon

##### Legend
Chas17 said:
Just_Jon,
No, Thanks. Formula worked and I learned more having to manipulate it.
Hands on stuff. Thanks.
Chas

Replies
14
Views
608
Replies
1
Views
204
Replies
5
Views
192
Replies
8
Views
145
Replies
1
Views
204

1,186,169
Messages
5,956,342
Members
438,247
Latest member
UZev

### 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.

### Which adblocker are you using?

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

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