#### SUMPRODUCT #10

```Can anyone help me making use of the Formula "SUMPRODUCT" within the below
file?

Thank you very much
Gennaro

```
 0
Gennaro (8)
9/30/2007 6:53:00 PM
excel 39879 articles. 2 followers.

16 Replies
630 Views

Similar Articles

[PageSpeed] 47

```Very few contributors will download a file from an unknown source, so
you might not get much help. Can you describe what it is you are
trying to do and give examples of your formula - explain how it is not
working (error, wrong result etc)

Hope this helps.

Pete

On Sep 30, 7:53 pm, Gennaro <Genn...@discussions.microsoft.com> wrote:
> Can anyone help me making use of the Formula "SUMPRODUCT" within the below
> file?
>
>
> Thank you very much
> Gennaro

```
 0
pashurst (2576)
9/30/2007 8:13:44 PM
```Dear Pete - thank you for your reply.

A	B	C
000001	00001001	-32644.52

The result in Sheet 2 says:
A	B	C

Hope this is not too confusing.

Regards
Gennaro
"Pete_UK" wrote:

> Very few contributors will download a file from an unknown source, so
> you might not get much help. Can you describe what it is you are
> trying to do and give examples of your formula - explain how it is not
> working (error, wrong result etc)
>
> Hope this helps.
>
> Pete
>
> On Sep 30, 7:53 pm, Gennaro <Genn...@discussions.microsoft.com> wrote:
> > Can anyone help me making use of the Formula "SUMPRODUCT" within the below
> > file?
> >
> >
> > Thank you very much
> > Gennaro
>
>
>
```
 0
Gennaro (8)
9/30/2007 8:31:02 PM
```You can't use whole columns in SUMPRODUCT() functions.  Use a smaller range
which is bigger then you will ever use but still short of a full column.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

sandymann2@mailinator.com
Replace @mailinator.com with @tiscali.co.uk

"Gennaro" <Gennaro@discussions.microsoft.com> wrote in message
news:7256A8F8-4D10-4543-A4EB-94664F63E8EE@microsoft.com...
>
> A B C
> 000001 00001001 -32644.52
>
> The result in Sheet 2 says:
> A B C
> 000001 00001001
>
> Hope this is not too confusing.
>
> Regards
> Gennaro
> "Pete_UK" wrote:
>
>> Very few contributors will download a file from an unknown source, so
>> you might not get much help. Can you describe what it is you are
>> trying to do and give examples of your formula - explain how it is not
>> working (error, wrong result etc)
>>
>> Hope this helps.
>>
>> Pete
>>
>> On Sep 30, 7:53 pm, Gennaro <Genn...@discussions.microsoft.com> wrote:
>> > Can anyone help me making use of the Formula "SUMPRODUCT" within the
>> > below
>> > file?
>> >
>> >
>> > Thank you very much
>> > Gennaro
>>
>>
>>
>

```
 0
sandymann2 (1054)
9/30/2007 9:13:20 PM
```I haven't looked at your file, but ... at first glance ... unless you're
using XL07, your formula *cannot* contain entire column references (A:A,
B:B, ...etc.).

Start off by changing that in your formula to realistic range sizes, and see
what happens.

--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Gennaro" <Gennaro@discussions.microsoft.com> wrote in message
news:7256A8F8-4D10-4543-A4EB-94664F63E8EE@microsoft.com...
>
> A B C
> 000001 00001001 -32644.52
>
> The result in Sheet 2 says:
> A B C
> 000001 00001001
>
> Hope this is not too confusing.
>
> Regards
> Gennaro
> "Pete_UK" wrote:
>
> > Very few contributors will download a file from an unknown source, so
> > you might not get much help. Can you describe what it is you are
> > trying to do and give examples of your formula - explain how it is not
> > working (error, wrong result etc)
> >
> > Hope this helps.
> >
> > Pete
> >
> > On Sep 30, 7:53 pm, Gennaro <Genn...@discussions.microsoft.com> wrote:
> > > Can anyone help me making use of the Formula "SUMPRODUCT" within the
below
> > > file?
> > >
> > >
> > > Thank you very much
> > > Gennaro
> >
> >
> >

```
 0
ragdyer1 (4060)
9/30/2007 9:15:23 PM
```Thank you to Sandy and Ragdyer. I've now changed the range sizes and the
result returned by the formula is equal to #VALUE!

Not sure what elase i can possibly try!

Regards
Gennaro

"Ragdyer" wrote:

> I haven't looked at your file, but ... at first glance ... unless you're
> using XL07, your formula *cannot* contain entire column references (A:A,
> B:B, ...etc.).
>
> Start off by changing that in your formula to realistic range sizes, and see
> what happens.
>
> --
> Regards,
>
> RD
>
> ---------------------------------------------------------------------------
> Please keep all correspondence within the NewsGroup, so all may benefit !
> ---------------------------------------------------------------------------
> "Gennaro" <Gennaro@discussions.microsoft.com> wrote in message
> news:7256A8F8-4D10-4543-A4EB-94664F63E8EE@microsoft.com...
> >
> > A B C
> > 000001 00001001 -32644.52
> >
> > The result in Sheet 2 says:
> > A B C
> > 000001 00001001
> >
> > Hope this is not too confusing.
> >
> > Regards
> > Gennaro
> > "Pete_UK" wrote:
> >
> > > Very few contributors will download a file from an unknown source, so
> > > you might not get much help. Can you describe what it is you are
> > > trying to do and give examples of your formula - explain how it is not
> > > working (error, wrong result etc)
> > >
> > > Hope this helps.
> > >
> > > Pete
> > >
> > > On Sep 30, 7:53 pm, Gennaro <Genn...@discussions.microsoft.com> wrote:
> > > > Can anyone help me making use of the Formula "SUMPRODUCT" within the
> below
> > > > file?
> > > >
> > > >
> > > > Thank you very much
> > > > Gennaro
> > >
> > >
> > >
>
>
```
 0
Gennaro (8)
9/30/2007 9:39:02 PM
```Besides what has been said, if you want to count use
to SUM c based on a & b use

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
dguillett1@austin.rr.com
"Gennaro" <Gennaro@discussions.microsoft.com> wrote in message
news:7256A8F8-4D10-4543-A4EB-94664F63E8EE@microsoft.com...
>
> A B C
> 000001 00001001 -32644.52
>
> The result in Sheet 2 says:
> A B C
> 000001 00001001
>
> Hope this is not too confusing.
>
> Regards
> Gennaro
> "Pete_UK" wrote:
>
>> Very few contributors will download a file from an unknown source, so
>> you might not get much help. Can you describe what it is you are
>> trying to do and give examples of your formula - explain how it is not
>> working (error, wrong result etc)
>>
>> Hope this helps.
>>
>> Pete
>>
>> On Sep 30, 7:53 pm, Gennaro <Genn...@discussions.microsoft.com> wrote:
>> > Can anyone help me making use of the Formula "SUMPRODUCT" within the
>> > below
>> > file?
>> >
>> >
>> > Thank you very much
>> > Gennaro
>>
>>
>>

```
 0
dguillett1 (2487)
9/30/2007 9:42:36 PM
```Usually when I get #VALUE! errors it is because I am trying to do maths on
text.  Do you have text in any of your ranges.  If they look like numbers
you can test with =ISTEXT(A1) and copy down & across.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

sandymann2@mailinator.com
Replace @mailinator.com with @tiscali.co.uk

"Gennaro" <Gennaro@discussions.microsoft.com> wrote in message
news:D3120797-34B3-4A96-85FA-0D1B1C23906A@microsoft.com...
> Thank you to Sandy and Ragdyer. I've now changed the range sizes and the
> result returned by the formula is equal to #VALUE!
>
> Not sure what elase i can possibly try!
>
> Regards
> Gennaro
>
> "Ragdyer" wrote:
>
>> I haven't looked at your file, but ... at first glance ... unless you're
>> using XL07, your formula *cannot* contain entire column references (A:A,
>> B:B, ...etc.).
>>
>> Start off by changing that in your formula to realistic range sizes, and
>> see
>> what happens.
>>
>> --
>> Regards,
>>
>> RD
>>
>> ---------------------------------------------------------------------------
>> Please keep all correspondence within the NewsGroup, so all may benefit !
>> ---------------------------------------------------------------------------
>> "Gennaro" <Gennaro@discussions.microsoft.com> wrote in message
>> news:7256A8F8-4D10-4543-A4EB-94664F63E8EE@microsoft.com...
>> >
>> > A B C
>> > 000001 00001001 -32644.52
>> >
>> > The result in Sheet 2 says:
>> > A B C
>> > 000001 00001001
>> >
>> > Hope this is not too confusing.
>> >
>> > Regards
>> > Gennaro
>> > "Pete_UK" wrote:
>> >
>> > > Very few contributors will download a file from an unknown source, so
>> > > you might not get much help. Can you describe what it is you are
>> > > trying to do and give examples of your formula - explain how it is
>> > > not
>> > > working (error, wrong result etc)
>> > >
>> > > Hope this helps.
>> > >
>> > > Pete
>> > >
>> > > On Sep 30, 7:53 pm, Gennaro <Genn...@discussions.microsoft.com>
>> > > wrote:
>> > > > Can anyone help me making use of the Formula "SUMPRODUCT" within
>> > > > the
>> below
>> > > > file?
>> > > >
>> > > >
>> > > > Thank you very much
>> > > > Gennaro
>> > >
>> > >
>> > >
>>
>>
>

```
 0
sandymann2 (1054)
9/30/2007 9:52:28 PM
```Using the asterisk form of Sumproduct,

*ANY* alpha characters in the calculating range (Column C), will cause the
#Value! error, BUT ... any text numbers will *still* calculate *accurately*.
If it looks like a number, the asterisk form will calculate it.
Also, if you are populating your calc range (Column C) with formulas that
equate to a zero length string ( "" ), this will also cause the #Value!
error.

You can try revising your formula to the unary form,

)

which will by-pass alpha and null entries.

Note, the unary form will by-pass text numbers also with no notification.
It will just calculate *true XL* numbers only.

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Gennaro" <Gennaro@discussions.microsoft.com> wrote in message
news:D3120797-34B3-4A96-85FA-0D1B1C23906A@microsoft.com...
> Thank you to Sandy and Ragdyer. I've now changed the range sizes and the
> result returned by the formula is equal to #VALUE!
>
> Not sure what elase i can possibly try!
>
> Regards
> Gennaro
>
> "Ragdyer" wrote:
>
> > I haven't looked at your file, but ... at first glance ... unless you're
> > using XL07, your formula *cannot* contain entire column references (A:A,
> > B:B, ...etc.).
> >
> > Start off by changing that in your formula to realistic range sizes, and
see
> > what happens.
> >
> > --
> > Regards,
> >
> > RD
> >
>
> --------------------------------------------------------------------------
-
> > Please keep all correspondence within the NewsGroup, so all may benefit
!
>
> --------------------------------------------------------------------------
-
> > "Gennaro" <Gennaro@discussions.microsoft.com> wrote in message
> > news:7256A8F8-4D10-4543-A4EB-94664F63E8EE@microsoft.com...
> > >
> > > A B C
> > > 000001 00001001 -32644.52
> > >
> > > The result in Sheet 2 says:
> > > A B C
> > > 000001 00001001
> > >
> > > Hope this is not too confusing.
> > >
> > > Regards
> > > Gennaro
> > > "Pete_UK" wrote:
> > >
> > > > Very few contributors will download a file from an unknown source,
so
> > > > you might not get much help. Can you describe what it is you are
> > > > trying to do and give examples of your formula - explain how it is
not
> > > > working (error, wrong result etc)
> > > >
> > > > Hope this helps.
> > > >
> > > > Pete
> > > >
> > > > On Sep 30, 7:53 pm, Gennaro <Genn...@discussions.microsoft.com>
wrote:
> > > > > Can anyone help me making use of the Formula "SUMPRODUCT" within
the
> > below
> > > > > file?
> > > > >
> > > > >
> > > > > Thank you very much
> > > > > Gennaro
> > > >
> > > >
> > > >
> >
> >

```
 0
ragdyer1 (4060)
9/30/2007 10:21:15 PM
```Thank you Sandy - How shall i insert the "ISTEXT" fomula within SUMPRODUCT
formula? I'm not sure how to link both formula.

Thank you again, Gennaro

"Sandy Mann" wrote:

> Usually when I get #VALUE! errors it is because I am trying to do maths on
> text.  Do you have text in any of your ranges.  If they look like numbers
> you can test with =ISTEXT(A1) and copy down & across.
>
> --
> HTH
>
> Sandy
> In Perth, the ancient capital of Scotland
> and the crowning place of kings
>
> sandymann2@mailinator.com
> Replace @mailinator.com with @tiscali.co.uk
>
>
> "Gennaro" <Gennaro@discussions.microsoft.com> wrote in message
> news:D3120797-34B3-4A96-85FA-0D1B1C23906A@microsoft.com...
> > Thank you to Sandy and Ragdyer. I've now changed the range sizes and the
> > result returned by the formula is equal to #VALUE!
> >
> > Not sure what elase i can possibly try!
> >
> > Regards
> > Gennaro
> >
> > "Ragdyer" wrote:
> >
> >> I haven't looked at your file, but ... at first glance ... unless you're
> >> using XL07, your formula *cannot* contain entire column references (A:A,
> >> B:B, ...etc.).
> >>
> >> Start off by changing that in your formula to realistic range sizes, and
> >> see
> >> what happens.
> >>
> >> --
> >> Regards,
> >>
> >> RD
> >>
> >> ---------------------------------------------------------------------------
> >> Please keep all correspondence within the NewsGroup, so all may benefit !
> >> ---------------------------------------------------------------------------
> >> "Gennaro" <Gennaro@discussions.microsoft.com> wrote in message
> >> news:7256A8F8-4D10-4543-A4EB-94664F63E8EE@microsoft.com...
> >> >
> >> > A B C
> >> > 000001 00001001 -32644.52
> >> >
> >> > The result in Sheet 2 says:
> >> > A B C
> >> > 000001 00001001
> >> >
> >> > Hope this is not too confusing.
> >> >
> >> > Regards
> >> > Gennaro
> >> > "Pete_UK" wrote:
> >> >
> >> > > Very few contributors will download a file from an unknown source, so
> >> > > you might not get much help. Can you describe what it is you are
> >> > > trying to do and give examples of your formula - explain how it is
> >> > > not
> >> > > working (error, wrong result etc)
> >> > >
> >> > > Hope this helps.
> >> > >
> >> > > Pete
> >> > >
> >> > > On Sep 30, 7:53 pm, Gennaro <Genn...@discussions.microsoft.com>
> >> > > wrote:
> >> > > > Can anyone help me making use of the Formula "SUMPRODUCT" within
> >> > > > the
> >> below
> >> > > > file?
> >> > > >
> >> > > >
> >> > > > Thank you very much
> >> > > > Gennaro
> >> > >
> >> > >
> >> > >
> >>
> >>
> >
>
>
>
```
 0
Gennaro (8)
9/30/2007 10:21:29 PM
```Hey Don, some typos?

Both formulas are identical as far as calculations are concerned.
Both will total Column C.

Counting might be:

OR, simply:

--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Don Guillett" <dguillett1@austin.rr.com> wrote in message
news:evGJcr6AIHA.2268@TK2MSFTNGP02.phx.gbl...
> Besides what has been said, if you want to count use
> to SUM c based on a & b use
>
>
>
>
> --
> Don Guillett
> Microsoft MVP Excel
> SalesAid Software
> dguillett1@austin.rr.com
> "Gennaro" <Gennaro@discussions.microsoft.com> wrote in message
> news:7256A8F8-4D10-4543-A4EB-94664F63E8EE@microsoft.com...
> >
> > A B C
> > 000001 00001001 -32644.52
> >
> > The result in Sheet 2 says:
> > A B C
> > 000001 00001001
> >
> > Hope this is not too confusing.
> >
> > Regards
> > Gennaro
> > "Pete_UK" wrote:
> >
> >> Very few contributors will download a file from an unknown source, so
> >> you might not get much help. Can you describe what it is you are
> >> trying to do and give examples of your formula - explain how it is not
> >> working (error, wrong result etc)
> >>
> >> Hope this helps.
> >>
> >> Pete
> >>
> >> On Sep 30, 7:53 pm, Gennaro <Genn...@discussions.microsoft.com> wrote:
> >> > Can anyone help me making use of the Formula "SUMPRODUCT" within the
> >> > below
> >> > file?
> >> >
> >> >
> >> > Thank you very much
> >> > Gennaro
> >>
> >>
> >>
>

```
 0
ragdyer1 (4060)
9/30/2007 10:27:33 PM
```Dear Ragdyer - Thank you for your help. Your latest suggestion does work but
somehow it’s not quite right. Can you be kind and have a look at the file
i've sent via email? It would be greatly appreciated.

Regards
Gennaro

"Ragdyer" wrote:

>
> Using the asterisk form of Sumproduct,
>
>
> *ANY* alpha characters in the calculating range (Column C), will cause the
> #Value! error, BUT ... any text numbers will *still* calculate *accurately*.
> If it looks like a number, the asterisk form will calculate it.
> Also, if you are populating your calc range (Column C) with formulas that
> equate to a zero length string ( "" ), this will also cause the #Value!
> error.
>
> You can try revising your formula to the unary form,
>
> )
>
> which will by-pass alpha and null entries.
>
> Note, the unary form will by-pass text numbers also with no notification.
> It will just calculate *true XL* numbers only.
>
> --
> HTH,
>
> RD
>
> ---------------------------------------------------------------------------
> Please keep all correspondence within the NewsGroup, so all may benefit !
> ---------------------------------------------------------------------------
> "Gennaro" <Gennaro@discussions.microsoft.com> wrote in message
> news:D3120797-34B3-4A96-85FA-0D1B1C23906A@microsoft.com...
> > Thank you to Sandy and Ragdyer. I've now changed the range sizes and the
> > result returned by the formula is equal to #VALUE!
> >
> > Not sure what elase i can possibly try!
> >
> > Regards
> > Gennaro
> >
> > "Ragdyer" wrote:
> >
> > > I haven't looked at your file, but ... at first glance ... unless you're
> > > using XL07, your formula *cannot* contain entire column references (A:A,
> > > B:B, ...etc.).
> > >
> > > Start off by changing that in your formula to realistic range sizes, and
> see
> > > what happens.
> > >
> > > --
> > > Regards,
> > >
> > > RD
> > >
> >
> > --------------------------------------------------------------------------
> -
> > > Please keep all correspondence within the NewsGroup, so all may benefit
> !
> >
> > --------------------------------------------------------------------------
> -
> > > "Gennaro" <Gennaro@discussions.microsoft.com> wrote in message
> > > news:7256A8F8-4D10-4543-A4EB-94664F63E8EE@microsoft.com...
> > > > Dear Pete - thank you for your reply.
> > > >
> > > > A B C
> > > > 000001 00001001 -32644.52
> > > >
> > > > The result in Sheet 2 says:
> > > > A B C
> > > > 000001 00001001
> > > >
> > > > Hope this is not too confusing.
> > > >
> > > > Regards
> > > > Gennaro
> > > > "Pete_UK" wrote:
> > > >
> > > > > Very few contributors will download a file from an unknown source,
> so
> > > > > you might not get much help. Can you describe what it is you are
> > > > > trying to do and give examples of your formula - explain how it is
> not
> > > > > working (error, wrong result etc)
> > > > >
> > > > > Hope this helps.
> > > > >
> > > > > Pete
> > > > >
> > > > > On Sep 30, 7:53 pm, Gennaro <Genn...@discussions.microsoft.com>
> wrote:
> > > > > > Can anyone help me making use of the Formula "SUMPRODUCT" within
> the
> > > below
> > > > > > file?
> > > > > >
> > > > > >
> > > > > > Thank you very much
> > > > > > Gennaro
> > > > >
> > > > >
> > > > >
> > >
> > >
>
>
```
 0
Gennaro (8)
9/30/2007 11:02:00 PM
```Maybe you mean in C2 in Sheet2:
with C2 then copied across and filled down to populate
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

```
 0
demechanik (4694)
10/1/2007 12:04:35 AM
```=SUMPRODUCT(--(Download!A2:A4000=Sheet2!A2),--(Download!B2:B4000=B2),(Download!C2:C4000))

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Gennaro" <Gennaro@discussions.microsoft.com> wrote in message
news:25AE290B-45B1-4E07-8D75-7CF85C36D042@microsoft.com...
> Thank you Sandy - How shall i insert the "ISTEXT" fomula within SUMPRODUCT
> formula? I'm not sure how to link both formula.
>
> Thank you again, Gennaro
>
> "Sandy Mann" wrote:
>
>> Usually when I get #VALUE! errors it is because I am trying to do maths
>> on
>> text.  Do you have text in any of your ranges.  If they look like numbers
>> you can test with =ISTEXT(A1) and copy down & across.
>>
>> --
>> HTH
>>
>> Sandy
>> In Perth, the ancient capital of Scotland
>> and the crowning place of kings
>>
>> sandymann2@mailinator.com
>> Replace @mailinator.com with @tiscali.co.uk
>>
>>
>> "Gennaro" <Gennaro@discussions.microsoft.com> wrote in message
>> news:D3120797-34B3-4A96-85FA-0D1B1C23906A@microsoft.com...
>> > Thank you to Sandy and Ragdyer. I've now changed the range sizes and
>> > the
>> > result returned by the formula is equal to #VALUE!
>> >
>> > Not sure what elase i can possibly try!
>> >
>> > Regards
>> > Gennaro
>> >
>> > "Ragdyer" wrote:
>> >
>> >> I haven't looked at your file, but ... at first glance ... unless
>> >> you're
>> >> using XL07, your formula *cannot* contain entire column references
>> >> (A:A,
>> >> B:B, ...etc.).
>> >>
>> >> Start off by changing that in your formula to realistic range sizes,
>> >> and
>> >> see
>> >> what happens.
>> >>
>> >> --
>> >> Regards,
>> >>
>> >> RD
>> >>
>> >> ---------------------------------------------------------------------------
>> >> Please keep all correspondence within the NewsGroup, so all may
>> >> benefit !
>> >> ---------------------------------------------------------------------------
>> >> "Gennaro" <Gennaro@discussions.microsoft.com> wrote in message
>> >> news:7256A8F8-4D10-4543-A4EB-94664F63E8EE@microsoft.com...
>> >> >
>> >> > A B C
>> >> > 000001 00001001 -32644.52
>> >> >
>> >> > The result in Sheet 2 says:
>> >> > A B C
>> >> > 000001 00001001
>> >> >
>> >> > Hope this is not too confusing.
>> >> >
>> >> > Regards
>> >> > Gennaro
>> >> > "Pete_UK" wrote:
>> >> >
>> >> > > Very few contributors will download a file from an unknown source,
>> >> > > so
>> >> > > you might not get much help. Can you describe what it is you are
>> >> > > trying to do and give examples of your formula - explain how it is
>> >> > > not
>> >> > > working (error, wrong result etc)
>> >> > >
>> >> > > Hope this helps.
>> >> > >
>> >> > > Pete
>> >> > >
>> >> > > On Sep 30, 7:53 pm, Gennaro <Genn...@discussions.microsoft.com>
>> >> > > wrote:
>> >> > > > Can anyone help me making use of the Formula "SUMPRODUCT" within
>> >> > > > the
>> >> below
>> >> > > > file?
>> >> > > >
>> >> > > >
>> >> > > > Thank you very much
>> >> > > > Gennaro
>> >> > >
>> >> > >
>> >> > >
>> >>
>> >>
>> >
>>
>>
>>

```
 0
bob.NGs1 (1661)
10/1/2007 8:16:03 AM
```It was late
count
--
Don Guillett
Microsoft MVP Excel
SalesAid Software
dguillett1@austin.rr.com
"Ragdyer" <RagDyer@cutoutmsn.com> wrote in message
news:%23v1WmF7AIHA.4712@TK2MSFTNGP04.phx.gbl...
> Hey Don, some typos?
>
> Both formulas are identical as far as calculations are concerned.
> Both will total Column C.
>
> Counting might be:
>
> OR, simply:
>
> --
> Regards,
>
> RD
>
> ---------------------------------------------------------------------------
> Please keep all correspondence within the NewsGroup, so all may benefit !
> ---------------------------------------------------------------------------
> "Don Guillett" <dguillett1@austin.rr.com> wrote in message
> news:evGJcr6AIHA.2268@TK2MSFTNGP02.phx.gbl...
>> Besides what has been said, if you want to count use
>> to SUM c based on a & b use
>>
>>
>>
>>
>> --
>> Don Guillett
>> Microsoft MVP Excel
>> SalesAid Software
>> dguillett1@austin.rr.com
>> "Gennaro" <Gennaro@discussions.microsoft.com> wrote in message
>> news:7256A8F8-4D10-4543-A4EB-94664F63E8EE@microsoft.com...
>> >
>> > A B C
>> > 000001 00001001 -32644.52
>> >
>> > The result in Sheet 2 says:
>> > A B C
>> > 000001 00001001
>> >
>> > Hope this is not too confusing.
>> >
>> > Regards
>> > Gennaro
>> > "Pete_UK" wrote:
>> >
>> >> Very few contributors will download a file from an unknown source, so
>> >> you might not get much help. Can you describe what it is you are
>> >> trying to do and give examples of your formula - explain how it is not
>> >> working (error, wrong result etc)
>> >>
>> >> Hope this helps.
>> >>
>> >> Pete
>> >>
>> >> On Sep 30, 7:53 pm, Gennaro <Genn...@discussions.microsoft.com> wrote:
>> >> > Can anyone help me making use of the Formula "SUMPRODUCT" within the
>> >> > below
>> >> > file?
>> >> >
>> >> >
>> >> > Thank you very much
>> >> > Gennaro
>> >>
>> >>
>> >>
>>
>

```
 0
dguillett1 (2487)
10/1/2007 12:09:20 PM
```Max - Thank you very very much. You have been once again very helpful.

Kind regards
Gennaro

"Max" wrote:

> Maybe you mean in C2 in Sheet2:
> with C2 then copied across and filled down to populate
> --
> Max
> Singapore
> http://savefile.com/projects/236895
> xdemechanik
> ---
>
>
>
```
 0
Gennaro (8)
10/1/2007 10:28:01 PM
```Besides the fact that I have not received any e-mails on this subject, it's
usually standard procedure in these groups to *first* ask permission,
especially since my signature includes a statement to the fact of keeping
the discussions public.

However, if you wish, you can send a *small* file.
Just cut out cutout from my address.
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Gennaro" <Gennaro@discussions.microsoft.com> wrote in message
news:15258BB7-54DE-4BC0-9D3F-2F06826A8512@microsoft.com...
> Dear Ragdyer - Thank you for your help. Your latest suggestion does work
> but
> somehow it's not quite right. Can you be kind and have a look at the file
> i've sent via email? It would be greatly appreciated.
>
> Regards
> Gennaro
>
>
> "Ragdyer" wrote:
>
>>
>> Using the asterisk form of Sumproduct,
>>
>>
>> *ANY* alpha characters in the calculating range (Column C), will cause
>> the
>> #Value! error, BUT ... any text numbers will *still* calculate
>> *accurately*.
>> If it looks like a number, the asterisk form will calculate it.
>> Also, if you are populating your calc range (Column C) with formulas that
>> equate to a zero length string ( "" ), this will also cause the #Value!
>> error.
>>
>> You can try revising your formula to the unary form,
>>
>> )
>>
>> which will by-pass alpha and null entries.
>>
>> Note, the unary form will by-pass text numbers also with no notification.
>> It will just calculate *true XL* numbers only.
>>
>> --
>> HTH,
>>
>> RD
>>
>> ---------------------------------------------------------------------------
>> Please keep all correspondence within the NewsGroup, so all may benefit !
>> ---------------------------------------------------------------------------
>> "Gennaro" <Gennaro@discussions.microsoft.com> wrote in message
>> news:D3120797-34B3-4A96-85FA-0D1B1C23906A@microsoft.com...
>> > Thank you to Sandy and Ragdyer. I've now changed the range sizes and
>> > the
>> > result returned by the formula is equal to #VALUE!
>> >
>> > Not sure what elase i can possibly try!
>> >
>> > Regards
>> > Gennaro
>> >
>> > "Ragdyer" wrote:
>> >
>> > > I haven't looked at your file, but ... at first glance ... unless
>> > > you're
>> > > using XL07, your formula *cannot* contain entire column references
>> > > (A:A,
>> > > B:B, ...etc.).
>> > >
>> > > Start off by changing that in your formula to realistic range sizes,
>> > > and
>> see
>> > > what happens.
>> > >
>> > > --
>> > > Regards,
>> > >
>> > > RD
>> > >
>> >
>> > --------------------------------------------------------------------------
>> -
>> > > Please keep all correspondence within the NewsGroup, so all may
>> > > benefit
>> !
>> >
>> > --------------------------------------------------------------------------
>> -
>> > > "Gennaro" <Gennaro@discussions.microsoft.com> wrote in message
>> > > news:7256A8F8-4D10-4543-A4EB-94664F63E8EE@microsoft.com...
>> > > > Dear Pete - thank you for your reply.
>> > > >
>> > > > A B C
>> > > > 000001 00001001 -32644.52
>> > > >
>> > > > The result in Sheet 2 says:
>> > > > A B C
>> > > > 000001 00001001
>> > > >
>> > > > Hope this is not too confusing.
>> > > >
>> > > > Regards
>> > > > Gennaro
>> > > > "Pete_UK" wrote:
>> > > >
>> > > > > Very few contributors will download a file from an unknown
>> > > > > source,
>> so
>> > > > > you might not get much help. Can you describe what it is you are
>> > > > > trying to do and give examples of your formula - explain how it
>> > > > > is
>> not
>> > > > > working (error, wrong result etc)
>> > > > >
>> > > > > Hope this helps.
>> > > > >
>> > > > > Pete
>> > > > >
>> > > > > On Sep 30, 7:53 pm, Gennaro <Genn...@discussions.microsoft.com>
>> wrote:
>> > > > > > Can anyone help me making use of the Formula "SUMPRODUCT"
>> > > > > > within
>> the
>> > > below
>> > > > > > file?
>> > > > > >
>> > > > > >
>> > > > > > Thank you very much
>> > > > > > Gennaro
>> > > > >
>> > > > >
>> > > > >
>> > >
>> > >
>>
>>

```
 0
ragdyer1 (4060)
10/1/2007 10:59:24 PM

Similar Artilces:

Moving email from one computer to another 02-12-10
I'm trying to move my email on my old computer (Windows Mail, Vista) to my new computer (Windows Live Mail, 7). I have read numerous posts and replies and nothing I've tried has worked for me. Any suggestions would be greatly appreciated. Thanks. You posted the same issue yesterday (twice), and I responded yesterday. --=20 Gary VanderMolen, Microsoft MVP (Mail) Microsoft MVP Program: http://mvp.support.microsoft.com "Shel421" <Shel421@discussions.microsoft.com> wrote in message = news:C36C437A-B016-413B-B231-BD80AD57BF99@microsoft.com... > I'm...

Accpac DOS with Adagio Migration to Dynamics 10
Anybody still do these migrations.... ...

noticias 04-19-10
ol� ...

Sumproduct
Hi all, I should make a condition(AND) in my sumproduct formula with date format(yyyy/mm/dd), why it dosent accept my condition in one column, like this: =SUMPRODUCT((\$B\$30:\$B\$3000=\$A\$1)*(\$G\$30:\$G\$3000>=\$B\$1)*(\$G\$30:\$G\$3000<\$B\$2)*(\$J\$30:\$J\$3000)) even I dublicate date column(G) and change the formula to : =SUMPRODUCT((\$B\$30:\$B\$3000=\$A\$1)*(\$G\$30:\$G\$3000>=\$B\$1)*(\$H\$30:\$H\$3000<\$B\$2)*(\$J\$30:\$J\$3000)) but it dosen't work again, any idea to solve this problem would be appreciated. Bijan Hi Bijan I don't see anyting wrong with your formula. Instead of multipl...

Sumproduct or??
If I have two columns of numbers: 1 50 2 40 1 20 4 10 3 30 1 50 How do I write a formula that will sum every number in column B that corresponds to a 1 in column A. The answer should be 120. Thanks for any help. I believe you will want to use a sumif() function here if your data starts in A1 then =sumif(A1:A6,1,B1:B6) On Dec 8, 10:19 am, Terry <Terr...@aol.com> wrote: > If I have two columns of numbers: > > 1 50 > 2 40 > 1 20 > 4 10 > 3 30 > 1 50 > > How do I write a formula that will sum every number in column B that > correspon...

physical inventory #10
I used physical inventory to do my stock count. Every thing is counted and entered. My prolem is that i do not have all the cost of each item in my data base, therefore i would like to use the selling price and work backwards to get to my total cost of the physical inventory. Does anyone knows how to get the total selling price of the physical inventory onto a report. Thanks inadvance Do you have a good physical inventory adjustment report? I have one that I can send you. What is your email address? -- Jocelyn "dexter" wrote: > I used physical inventory to do my stock...

access report 03-29-10
i have a combo box in a form to select 1 of 5 values and i setup list so each selection has a number value ie, 1. table 2.dessert etc. i have check boxes on the report that have to be checked based on the value selected on the form. i have to show all check boxes on the report but only check the box for the value choose in fhe form. i have tried to do conditional statement on the report and check for numeric value and then set check value to true based on that but haven't had any luck. any help greatly appreciated. Submitted via EggHeadCafe - Software Developer Portal of Choice ...

If SUMPRODUCT & Blank cells
I am running Excel 2003 and I am trying to count a range of data for charting. I am using the following formula: '=IF(SUMPRODUCT(('Assess'!\$H\$3:\$H\$189="Temp")*('Assess'!\$J\$3:\$J\$189<\$B \$15)*('Assess'!\$M\$3:\$M\$189="N")*('Assess'!\$A\$3:\$A\$189=A16) *1),SUMPRODUCT(('Assess'!\$H\$3:\$H\$189="Temp")*('Assess'!\$J\$3:\$J\$189<\$B \$15)*('Assess'!\$M\$3:\$M\$189="N")*('Assess'!\$A\$3:\$A\$189=A16)*1),0) It works great....except... In column \$M\$3:\$M\$189 there are also blank cells and I want to count these a...

SUMPRODUCT and OR?
How do you count rows from criterias in two columns where the criteri shall be OR? I.e. something lik SUMPRODUCT(('[jisses.xls]Requirements'!\$E\$2:\$E\$10000="Car")+('[jisses.xls]Requirements'!\$F\$2:\$F\$10000="Car")) but where you get a count on number of rows where either (both column are = Car) or (any of the columns are = Car)? -- Message posted from http://www.ExcelForum.com Rune, Try =SUMPRODUCT(('[jisses.xls]Requirements'!\$E\$2:\$E\$10000="Car")+('[jisses.xls]R equirements'!\$F\$2:\$F\$10000="Car"))-SUMPRODUCT(('[jisses...

Update Report 03-02-10
I have a report that doesnt show all of my data from the table. It worked the first time, then yesterday I added more entries. It only shows the older entries now. I'm sure this is probably something simple that I'm overlooking, but can someone please tell me how to get my Report to update so that it shows all records in the table every time? Thanks for your help!! Open the report in design view and find the record source. View the record source in design and try to troubleshoot. If you can't figure out your problem, come back with what you have discovered. -- ...

Name of Address table in Great Plains 10
HI: What is the table name that holds the Vendor Address? I don't have SQL in front of me but my documentation tells me that the Vendor Master is PM00200 (PM_Vendor_MSTR) and the Vendor Address Master is PM00300 (PM_Address_MSTR) "janieb" wrote: > HI: > > What is the table name that holds the Vendor Address? > > ...

Help Debug Complex Formula (SUMPRODUCT? SUMIF?)
Folks, I'm really struggling with this one. I've got 354 rows of data (rows 3:352). In column G there may be a date. In column M there is a number (1-12) which represents a monthly period, and in column O there is a dollar amount which represents a montly invoice total. I need to construct a formula which calculates the sum of O for a specific period M where there is a date entered (non-blank cell) in G. The formula below is what I constructed but it does not work. Rather it calculates the sum of O for the specified period in M but then multiplies the sum (\$16,200) by the num...

How to merge 10 files in one step?
Hi Everyone I would like to check is it possible to merge 10 files with the same format together with one step or less steps when compared to opening up 10 files and then copy and paste? I would really appreciate any help given... Cheers ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~View and post usenet messages directly from http://www.ExcelForum.com/ ~~Now Available: Financial Statements.xls, a step by step guide to creating financial statements There is no way built into Excel to do a merge other than copy/paste. (Well, there is th...

Sumproduct or ?????
Hi All, I have 2 tables one is cust table and the other one is amount table. A B C D Customer Table Amount Table Parent ID Cust # Cust # Amount 2001 AA AA 5 2001 BB AA 5 2001 CC AA 5 2001 DD AA 5 2001 EE AA -5 2001 FF AA -5 2001 GG BB 3 BB 3 BB -3 Summary AA - I want to be able to count if "AA" in Cust table (Column B) then count positive amount minus negative amount in amount table (Column D). In this case the answer is "2" BB - The same thing with "BB". The answer is "1" Thank you ...

Increasing the speed of Sumproduct
Hi, 1. Just read http://www.mcgimpsey.com/excel/formulae/doubleneg.html In this JEM says that we have double negs so that =SUMPRODUCT(--(A1:A5>10),B1:B5)) can be coerced in to 1. As per JEM"s explanation single unary will coerce True/False to Zero/One and the second double unary is used so that the negative values could be converted to its original sign. My "reasoning" was instead of using double negative sign why not use a single + sign and achieve further speed increase. So I did this =SUMPRODUCT(--(A1:A5>5),--(A1:A5<10),B1:B5) . The data I used was ...

Filtering Tasks in Outlook 2002 (10.2627.3501) SP-1
I've attempted to a apply a simple filter to my task list without any success. I have tasks on my list that are due yesterday, today and tomorrow. View > Go To > Tasks View > Current View > Customize Current View... In the View Summary dialogue box I click on Automatic Formatting... In the Automatic Formatting dialogue box I click on Add. Under Properties of Selected Rule: - I type "Today's tasks" in the Name field. - I click the Font... button and select bold, green and click OK. - I click the Condition... button and select "due" and "tod...

Sumproduct??
Hi I have a range A1:S1 with a series of integers and a range A2:S2 with another series of integers. How can I count all the instances where the equivalent cell in the second range is equal to two less than that in the first range. eg 6 4 9 7 8 etc 4 3 7 8 7 etc the above would count 2 - Columns A and C Thanks in advance Sandy =SUMPRODUCT(--(A1:S1-A2:S2=2)) =sumproduct() likes to work with numbers. The -- stuff changes trues and falses to 1's and 0's. Bob Phillips explains =sumproduct() in much more detail here: http://www.xldynamic.com/source/...

SoundPlayer 01-04-10
Can someone explain why the soundplayer still works after a Dispose() = call SoundPlayer player =3D new SoundPlayer(); player.Stream =3D Properties.Resources.alarm; player.Play(); player.Dispose(); player.Play() // Should this still work??? mick On Jan 4, 11:46=A0am, "mick" <coughco...@privacy.com> wrote: > Can someone explain why the soundplayer still works after a Dispose() cal= l > > SoundPlayer player =3D new SoundPlayer(); > player.Stream =3D Properties.Resources.alarm; > player.Play(); > player.Dispose(); > player.P...

publisher 97 #10
I have a file that was oriniated in Publisher 97. I inadevertently opened and saved it in Publisher 2002 on a different computer(using server). Now I can't access it through 97. Is there a way to do this? Can't save as, as the files of type don't say 97. Maybe they were the same as Publisher 98 files. Worth a try. -- Don Vancouver, USA "Terry" <Terry@discussions.microsoft.com> wrote in message news:D0B451AE-ACF2-449B-8F11-4F0C16297A12@microsoft.com... > I have a file that was oriniated in Publisher 97. I inadevertently opened > and saved it in P...

extra line number 01-11-10
With Word 2003 I didn't have a problem, but ever since I've had 2007, when I have a page break, although it doesn't show a line number on the screen nor on the print preview, it will print a line number on the line with the page break. I can fix some of these by deleting the paragraph before the line break, but if I have different alignments on the preceding and succeeding lines, that doesn't work. To keep the formatting correct, I must have that paragraph before the page break. Either being able to fix the line number printing or being able to fix the formatti...

Word Document 01-14-10
I have two Word documents saved. Everytime I access one of these documents to make a change, the cursor just freezes as soon as I hit any key. I have to hit Ctnl, Alt, Del to exit the document because the entire page is locked up. What causes this and how do I get by this freezing? Thanks. -- tramonte50 See http://word.mvps.org/FAQs/AppErrors/CorruptDoc.htm. -- Regards, Jay Freedman Microsoft Word MVP FAQ: http://word.mvps.org Email cannot be acknowledged; please post all follow-ups to the newsgroup so all may benefit. tramonte50 wrote: > I have two Word ...

MS Dynamics GP 10.0 SP3
Dear All Previously i've managed to successfully install Workflow on a client's live server. This is after 1-week of many support calls and emails. Now funnily enough; i'm replicating the same installation procedures on 2 virtual server images. Here's the basics Server 1 (AD/Domain Controller) ----------------------------------------- Windows Server 2003 Std Ed SQL Server 2008 GP 10.0 Server 2 (Webserver) ----------------------------------------- Windows Server 2003 Std Ed MOSS 2007 SP1 GP 10.0 - Web Services GP 10.0 - MS Sharepoint Wizard As it i...

setting up email 06-06-10
Does anyone know how to: 1. Set up or re-set your Windows Vista email account? Thank you, Renee' Carden Yes, I do. And your email account provider probably has instructions on their website. If they don't have them for Windows mail, follow the one for Outlook Express. -- Dave N. MS-MVP (Mail) Windows 7 Ultimate http://download.live.com/wlmail "Renee' Carden" <reneecarden@microsoft.com> wrote in message news:#O4ciabBLHA.5848@TK2MSFTNGP06.phx.gbl... > Does anyone know how to: > 1. Set up or re-set your Windows Vista email account?...

Sumproduct #16
I have 3 columns of data, NAME, DATE,VALUE NAME Date Text Carlos 1/1/4 10 Carlos 2/1/4 20 Carlos 5/6/4 30 Carlos 6/6/4 40 Peter 5/5/4 50 Peter 20/6/4 60 The forumla that I need to use is Look for CARLOS in the database, and retrieve VALUE when NAME is CARLO and DATE is the LATEST (for carlos of course). In this example if I look for Carlos the data I want is 40 If I look for Peter the data I want should be 60 The formula I tried was =sumproduct(NAMES=Carlos)*(DATES=MAX(DATES)),VALUE) I think it is because it doesn't match the latest date for carlos. ...

Windows Live Mail 04-28-10
Dies ist eine mehrteilige Nachricht im MIME-Format. ------=_NextPart_000_0046_01CAE6CC.2778DE70 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Hi folks, Have a short question. Is it possible to change the layout of Windows = Live Mail ??? I am talking about coloring the application. Those light = colors I do not like. I am more into black style. Maybe someone has read = something about an application to do it or some theme changing = application. Thanx in advance --=20 Alpha1968 I am the ALPHA and the OMEGA... Th...