sumproduct

Chas17

Well-known Member
Joined
Oct 16, 2002
Messages
657
=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

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

just_jon

Legend
Joined
Sep 3, 2002
Messages
10,473
Try --

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

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
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
Joined
Oct 16, 2002
Messages
657
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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209

ADVERTISEMENT

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
Joined
Sep 3, 2002
Messages
10,473
My apologies for not having bothered to actually read your clearly-stated post before responding. :oops:
 

Chas17

Well-known Member
Joined
Oct 16, 2002
Messages
657

ADVERTISEMENT

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

Well-known Member
Joined
Oct 16, 2002
Messages
657
Just_Jon,
No, Thanks. Formula worked and I learned more having to manipulate it.
Hands on stuff. Thanks.
Chas
 

just_jon

Legend
Joined
Sep 3, 2002
Messages
10,473
Chas17 said:
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! :LOL:
 
Learn Excel from Bill Jelen

Understanding data is crucial, and the easiest place to start is with Microsoft Excel.

Forum statistics

Threads
1,151,566
Messages
5,765,150
Members
425,264
Latest member
Towervibe

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top