PC Review


Reply
Thread Tools Rate Thread

SUMPRODUCT (I believe??)

 
 
=?Utf-8?B?Ri4gTGF3cmVuY2UgS3VsY2hhcg==?=
Guest
Posts: n/a
 
      14th Sep 2006
I have an array of numbers and/or text values in cells A1 through E5, such as:

A B C D E

1 12 6 A Y -5
2 4 2 3 AB 9
3 GH -1 -6 0 17
4 A V R -9 3
5 2 1 6 3 8

AND I WISH TO ADD THE CELLS where the values are 0 < X < 10. IGNORE ALL
OTHER CELL VALUES.

THEREFORE, my answer will be: 6 + 4 + 2 + 3 + 9 + 3 + 2 + 1 + 6 + 3 + 8 =
47.

Please, how is this done...using the =SUMPRODUCT formula...

It is something such as:

=SUMPRODUCT(AND(A1:E5>0,A1:E5<10),A1:E5)...BUT NOT QUITE SO!!!!!!!!!!!

PLEASE ADVISE.

Thank you,

FLKulchar
 
Reply With Quote
 
 
 
 
Franz Verga
Guest
Posts: n/a
 
      14th Sep 2006
F. Lawrence Kulchar wrote:
> I have an array of numbers and/or text values in cells A1 through E5,
> such as:
>
> A B C D E
>
> 1 12 6 A Y -5
> 2 4 2 3 AB 9
> 3 GH -1 -6 0 17
> 4 A V R -9 3
> 5 2 1 6 3 8
>
> AND I WISH TO ADD THE CELLS where the values are 0 < X < 10. IGNORE
> ALL OTHER CELL VALUES.
>
> THEREFORE, my answer will be: 6 + 4 + 2 + 3 + 9 + 3 + 2 + 1 + 6 + 3
> + 8 =
> 47.
>
> Please, how is this done...using the =SUMPRODUCT formula...
>
> It is something such as:
>
> =SUMPRODUCT(AND(A1:E5>0,A1:E5<10),A1:E5)...BUT NOT QUITE SO!!!!!!!!!!!
>
> PLEASE ADVISE.
>
> Thank you,
>
> FLKulchar



Hi Lawrence,

try with this formula, array entered, i.e. pressing Ctrl + Shift + Enter,
instead of just Enter.


=SUM((A1:E5>0)*(A1:E5<10)*IF(ISERROR(VALUE(A1:E5)),0,A1:E5))

--
(I'm not sure of names of menus, options and commands, because
translating from the Italian version of Excel...)

Hope I helped you.

Thanks in advance for your feedback.

Ciao

Franz Verga from Italy


 
Reply With Quote
 
 
 
 
=?Utf-8?B?U3RlZmk=?=
Guest
Posts: n/a
 
      14th Sep 2006
=SUMPRODUCT(A1:E5,--(A1:E5>0),--(A1:E5<10))
regards,
Stefi


„F. Lawrence Kulchar” ezt *rta:

> I have an array of numbers and/or text values in cells A1 through E5, such as:
>
> A B C D E
>
> 1 12 6 A Y -5
> 2 4 2 3 AB 9
> 3 GH -1 -6 0 17
> 4 A V R -9 3
> 5 2 1 6 3 8
>
> AND I WISH TO ADD THE CELLS where the values are 0 < X < 10. IGNORE ALL
> OTHER CELL VALUES.
>
> THEREFORE, my answer will be: 6 + 4 + 2 + 3 + 9 + 3 + 2 + 1 + 6 + 3 + 8 =
> 47.
>
> Please, how is this done...using the =SUMPRODUCT formula...
>
> It is something such as:
>
> =SUMPRODUCT(AND(A1:E5>0,A1:E5<10),A1:E5)...BUT NOT QUITE SO!!!!!!!!!!!
>
> PLEASE ADVISE.
>
> Thank you,
>
> FLKulchar

 
Reply With Quote
 
Bob Phillips
Guest
Posts: n/a
 
      14th Sep 2006
=SUMIF(A1:E5,">0")-SUMIF(A1:E5,">10")

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"F. Lawrence Kulchar" <(E-Mail Removed)> wrote in
message news:(E-Mail Removed)...
> I have an array of numbers and/or text values in cells A1 through E5, such

as:
>
> A B C D E
>
> 1 12 6 A Y -5
> 2 4 2 3 AB 9
> 3 GH -1 -6 0 17
> 4 A V R -9 3
> 5 2 1 6 3 8
>
> AND I WISH TO ADD THE CELLS where the values are 0 < X < 10. IGNORE ALL
> OTHER CELL VALUES.
>
> THEREFORE, my answer will be: 6 + 4 + 2 + 3 + 9 + 3 + 2 + 1 + 6 + 3 + 8

=
> 47.
>
> Please, how is this done...using the =SUMPRODUCT formula...
>
> It is something such as:
>
> =SUMPRODUCT(AND(A1:E5>0,A1:E5<10),A1:E5)...BUT NOT QUITE SO!!!!!!!!!!!
>
> PLEASE ADVISE.
>
> Thank you,
>
> FLKulchar



 
Reply With Quote
 
Bob Phillips
Guest
Posts: n/a
 
      14th Sep 2006
or even =SUMPRODUCT((A1:E5>0)*(A1:E5<10),A1:E5)

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Franz Verga" <(E-Mail Removed)> wrote in message
news:evQjeJ$(E-Mail Removed)...
> F. Lawrence Kulchar wrote:
> > I have an array of numbers and/or text values in cells A1 through E5,
> > such as:
> >
> > A B C D E
> >
> > 1 12 6 A Y -5
> > 2 4 2 3 AB 9
> > 3 GH -1 -6 0 17
> > 4 A V R -9 3
> > 5 2 1 6 3 8
> >
> > AND I WISH TO ADD THE CELLS where the values are 0 < X < 10. IGNORE
> > ALL OTHER CELL VALUES.
> >
> > THEREFORE, my answer will be: 6 + 4 + 2 + 3 + 9 + 3 + 2 + 1 + 6 + 3
> > + 8 =
> > 47.
> >
> > Please, how is this done...using the =SUMPRODUCT formula...
> >
> > It is something such as:
> >
> > =SUMPRODUCT(AND(A1:E5>0,A1:E5<10),A1:E5)...BUT NOT QUITE SO!!!!!!!!!!!
> >
> > PLEASE ADVISE.
> >
> > Thank you,
> >
> > FLKulchar

>
>
> Hi Lawrence,
>
> try with this formula, array entered, i.e. pressing Ctrl + Shift + Enter,
> instead of just Enter.
>
>
> =SUM((A1:E5>0)*(A1:E5<10)*IF(ISERROR(VALUE(A1:E5)),0,A1:E5))
>
> --
> (I'm not sure of names of menus, options and commands, because
> translating from the Italian version of Excel...)
>
> Hope I helped you.
>
> Thanks in advance for your feedback.
>
> Ciao
>
> Franz Verga from Italy
>
>



 
Reply With Quote
 
 
 
Reply

Thread Tools
Rate This Thread
Rate This Thread:

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Sumproduct instead of SumifS in VBA (application.sumproduct) Majken Bilslev-Jensen Microsoft Excel Programming 7 30th Dec 2010 06:56 PM
Sumproduct with Condition OR Sumproduct with ADDRESS function - HE gholly Microsoft Excel Misc 2 28th Sep 2009 05:07 PM
Conditional SUMPRODUCT or SUMPRODUCT with Filters Ted M H Microsoft Excel Worksheet Functions 4 14th Aug 2008 07:50 PM
sumproduct? sumif(sumproduct)? =?Utf-8?B?RGF2aWQ=?= Microsoft Excel Worksheet Functions 3 13th Jul 2007 07:06 PM
SUMPRODUCT - NO SUMPRODUCT! =?Utf-8?B?TWFyaw==?= Microsoft Excel Programming 3 23rd Nov 2005 03:36 PM


Features
 

Advertising
 

Newsgroups
 


All times are GMT +1. The time now is 10:28 AM.