Sumproduct Question

=SUMPRODUCT(($E$25:$E$2000=4)*($F$25:$F$2000=6.5)*(P25:P2000))

This above formula achieves the desired result.  However, when  I add
a fourth column (below) I get a Value error.

I think I'm close...any ideas?

=SUMPRODUCT(($E$25:$E$2000=4)*($F$25:$F$2000=6.5)*(P25:P2000)*($M$25:$M$2000))
0
JohnP26 (33)
1/10/2008 12:28:04 AM
excel 39879 articles. 2 followers. Follow

17 Replies
903 Views

Similar Articles

[PageSpeed] 20

First, I'd watch those addresses.  I bet you want to use $P$25:$P$2000, too.

Second, do you  have any text values in M25:M2000--or any errors in that range?

If you have some text, you can use a different formula to ignore text:

=SUMPRODUCT(--($E$25:$E$2000=4),
            --($F$25:$F$2000=6.5),
            $P$25:$P$2000,
            $M$25:$M$2000)

Adjust the ranges to match--but you can't use whole columns (except in xl2007). 

=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/xld.SUMPRODUCT.html

And J.E. McGimpsey has some notes at:
http://mcgimpsey.com/excel/formulae/doubleneg.html

===
ps.  When you're looking for text or errors, remember to look in hidden rows,
too.




JP wrote:
> 
> =SUMPRODUCT(($E$25:$E$2000=4)*($F$25:$F$2000=6.5)*(P25:P2000))
> 
> This above formula achieves the desired result.  However, when  I add
> a fourth column (below) I get a Value error.
> 
> I think I'm close...any ideas?
> 
> =SUMPRODUCT(($E$25:$E$2000=4)*($F$25:$F$2000=6.5)*(P25:P2000)*($M$25:$M$2000))

-- 

Dave Peterson
0
petersod (12004)
1/10/2008 12:37:27 AM
On Wed, 09 Jan 2008 18:37:27 -0600, Dave Peterson
<petersod@verizonXSPAM.net> wrote:

>First, I'd watch those addresses.  I bet you want to use $P$25:$P$2000, too.

No.  It was correct.
>
>Second, do you  have any text values in M25:M2000--or any errors in that range?

None.
>
>If you have some text, you can use a different formula to ignore text:
>
>=SUMPRODUCT(--($E$25:$E$2000=4),
>            --($F$25:$F$2000=6.5),
>            $P$25:$P$2000,
>            $M$25:$M$2000)

Doing it this way doesn't return the value error, but it gives
different answers.  I tallied the results by hand and for some reason
they are off.




>Adjust the ranges to match--but you can't use whole columns (except in xl2007). 
>
>=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/xld.SUMPRODUCT.html
>
>And J.E. McGimpsey has some notes at:
>http://mcgimpsey.com/excel/formulae/doubleneg.html
>
>===
>ps.  When you're looking for text or errors, remember to look in hidden rows,
>too.
>
>
>
>
>JP wrote:
>> 
>> =SUMPRODUCT(($E$25:$E$2000=4)*($F$25:$F$2000=6.5)*(P25:P2000))
>> 
>> This above formula achieves the desired result.  However, when  I add
>> a fourth column (below) I get a Value error.
>> 
>> I think I'm close...any ideas?
>> 
>> =SUMPRODUCT(($E$25:$E$2000=4)*($F$25:$F$2000=6.5)*(P25:P2000)*($M$25:$M$2000))

0
JohnP26 (33)
1/10/2008 10:20:32 AM
Are you sure that you don't have a #VALUE in one of the cells?

-- 
---
HTH

Bob


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



"JP" <JohnP26@msn.com> wrote in message 
news:n3sbo35gad0vla65c36d8d0pn5057tvt7d@4ax.com...
> On Wed, 09 Jan 2008 18:37:27 -0600, Dave Peterson
> <petersod@verizonXSPAM.net> wrote:
>
>>First, I'd watch those addresses.  I bet you want to use $P$25:$P$2000, 
>>too.
>
> No.  It was correct.
>>
>>Second, do you  have any text values in M25:M2000--or any errors in that 
>>range?
>
> None.
>>
>>If you have some text, you can use a different formula to ignore text:
>>
>>=SUMPRODUCT(--($E$25:$E$2000=4),
>>            --($F$25:$F$2000=6.5),
>>            $P$25:$P$2000,
>>            $M$25:$M$2000)
>
> Doing it this way doesn't return the value error, but it gives
> different answers.  I tallied the results by hand and for some reason
> they are off.
>
>
>
>
>>Adjust the ranges to match--but you can't use whole columns (except in 
>>xl2007).
>>
>>=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/xld.SUMPRODUCT.html
>>
>>And J.E. McGimpsey has some notes at:
>>http://mcgimpsey.com/excel/formulae/doubleneg.html
>>
>>===
>>ps.  When you're looking for text or errors, remember to look in hidden 
>>rows,
>>too.
>>
>>
>>
>>
>>JP wrote:
>>>
>>> =SUMPRODUCT(($E$25:$E$2000=4)*($F$25:$F$2000=6.5)*(P25:P2000))
>>>
>>> This above formula achieves the desired result.  However, when  I add
>>> a fourth column (below) I get a Value error.
>>>
>>> I think I'm close...any ideas?
>>>
>>> =SUMPRODUCT(($E$25:$E$2000=4)*($F$25:$F$2000=6.5)*(P25:P2000)*($M$25:$M$2000))
> 


0
bob.NGs1 (1661)
1/10/2008 11:46:52 AM
Yes I'm quite sure.  I checked manually, and also many other
computations are used with those columns of data and they return
proper answers.

On Thu, 10 Jan 2008 11:46:52 -0000, "Bob Phillips"
<bob.NGs@somewhere.com> wrote:

>Are you sure that you don't have a #VALUE in one of the cells?

0
JohnP26 (33)
1/10/2008 2:09:13 PM
If you put:
=counta(m25:m2000)
in an empty cell
and 
=count(m25:m2000)
in another 

What do these formulas return?



JP wrote:
> 
> On Wed, 09 Jan 2008 18:37:27 -0600, Dave Peterson
> <petersod@verizonXSPAM.net> wrote:
> 
> >First, I'd watch those addresses.  I bet you want to use $P$25:$P$2000, too.
> 
> No.  It was correct.
> >
> >Second, do you  have any text values in M25:M2000--or any errors in that range?
> 
> None.
> >
> >If you have some text, you can use a different formula to ignore text:
> >
> >=SUMPRODUCT(--($E$25:$E$2000=4),
> >            --($F$25:$F$2000=6.5),
> >            $P$25:$P$2000,
> >            $M$25:$M$2000)
> 
> Doing it this way doesn't return the value error, but it gives
> different answers.  I tallied the results by hand and for some reason
> they are off.
> 
> >Adjust the ranges to match--but you can't use whole columns (except in xl2007).
> >
> >=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/xld.SUMPRODUCT.html
> >
> >And J.E. McGimpsey has some notes at:
> >http://mcgimpsey.com/excel/formulae/doubleneg.html
> >
> >===
> >ps.  When you're looking for text or errors, remember to look in hidden rows,
> >too.
> >
> >
> >
> >
> >JP wrote:
> >>
> >> =SUMPRODUCT(($E$25:$E$2000=4)*($F$25:$F$2000=6.5)*(P25:P2000))
> >>
> >> This above formula achieves the desired result.  However, when  I add
> >> a fourth column (below) I get a Value error.
> >>
> >> I think I'm close...any ideas?
> >>
> >> =SUMPRODUCT(($E$25:$E$2000=4)*($F$25:$F$2000=6.5)*(P25:P2000)*($M$25:$M$2000))

-- 

Dave Peterson
0
petersod (12004)
1/10/2008 2:41:57 PM
Select that range
Edit|goto|special
Check constants and errors (and uncheck everything else)

Then try the same thing with formulas and errors.

If you have the data filtered, make sure you show all the rows.

JP wrote:
> 
> Yes I'm quite sure.  I checked manually, and also many other
> computations are used with those columns of data and they return
> proper answers.
> 
> On Thu, 10 Jan 2008 11:46:52 -0000, "Bob Phillips"
> <bob.NGs@somewhere.com> wrote:
> 
> >Are you sure that you don't have a #VALUE in one of the cells?

-- 

Dave Peterson
0
petersod (12004)
1/10/2008 2:51:19 PM
I did both and got an answer of "no cells were found."

On Thu, 10 Jan 2008 08:51:19 -0600, Dave Peterson
<petersod@verizonXSPAM.net> wrote:

>Select that range
>Edit|goto|special
>Check constants and errors (and uncheck everything else)
>
>Then try the same thing with formulas and errors.
>
>If you have the data filtered, make sure you show all the rows.
>
>JP wrote:
>> 
>> Yes I'm quite sure.  I checked manually, and also many other
>> computations are used with those columns of data and they return
>> proper answers.
>> 
>> On Thu, 10 Jan 2008 11:46:52 -0000, "Bob Phillips"
>> <bob.NGs@somewhere.com> wrote:
>> 
>> >Are you sure that you don't have a #VALUE in one of the cells?

0
JohnP26 (33)
1/10/2008 9:46:57 PM
221, and 118 respectively.

On Thu, 10 Jan 2008 08:41:57 -0600, Dave Peterson
<petersod@verizonXSPAM.net> wrote:

>=counta(m25:m2000)

0
JohnP26 (33)
1/10/2008 9:48:30 PM
That means that there are 3 values that are not numbers.

=counta() returns the number of cells with something in them.
=count() returns the number of cells with numbers in them.

Select the range
edit|goto|special
and look for numbers or text or blanks

Look in values or formulas or both.

JP wrote:
> 
> 221, and 118 respectively.
> 
> On Thu, 10 Jan 2008 08:41:57 -0600, Dave Peterson
> <petersod@verizonXSPAM.net> wrote:
> 
> >=counta(m25:m2000)

-- 

Dave Peterson
0
petersod (12004)
1/10/2008 10:06:06 PM
OK, checked all of that out.  This is the formula I have in the fist
cell. 

 =IF(I26="","",I26-H26)

I drag it down the column.  For every new day that data is entered I
skip a space.  I couldn't find the three cells.



On Thu, 10 Jan 2008 16:06:06 -0600, Dave Peterson
<petersod@verizonXSPAM.net> wrote:

>That means that there are 3 values that are not numbers.
>
>=counta() returns the number of cells with something in them.
>=count() returns the number of cells with numbers in them.
>
>Select the range
>edit|goto|special
>and look for numbers or text or blanks
>
>Look in values or formulas or both.
>
>JP wrote:
>> 
>> 221, and 118 respectively.
>> 
>> On Thu, 10 Jan 2008 08:41:57 -0600, Dave Peterson
>> <petersod@verizonXSPAM.net> wrote:
>> 
>> >=counta(m25:m2000)

0
JohnP26 (33)
1/10/2008 11:01:12 PM
Do you have the worksheet filtered?

Maybe your error or text value is in one of the hidden rows???



JP wrote:
> 
> OK, checked all of that out.  This is the formula I have in the fist
> cell.
> 
>  =IF(I26="","",I26-H26)
> 
> I drag it down the column.  For every new day that data is entered I
> skip a space.  I couldn't find the three cells.
> 
> On Thu, 10 Jan 2008 16:06:06 -0600, Dave Peterson
> <petersod@verizonXSPAM.net> wrote:
> 
> >That means that there are 3 values that are not numbers.
> >
> >=counta() returns the number of cells with something in them.
> >=count() returns the number of cells with numbers in them.
> >
> >Select the range
> >edit|goto|special
> >and look for numbers or text or blanks
> >
> >Look in values or formulas or both.
> >
> >JP wrote:
> >>
> >> 221, and 118 respectively.
> >>
> >> On Thu, 10 Jan 2008 08:41:57 -0600, Dave Peterson
> >> <petersod@verizonXSPAM.net> wrote:
> >>
> >> >=counta(m25:m2000)

-- 

Dave Peterson
0
petersod (12004)
1/10/2008 11:43:56 PM
You *should not* use the asterisk form of Sumproduct() when you have your 
calculating column(s) populated by formulas that may return zero length 
strings (nulls - "" ), as you *do* have!
These returns are considered TEXT, and will generate the #Value! error when 
Column I is empty, causing a null ( "" ) return.in Column M.

The asterisk form will calculate text numbers, as long as they look like 
numbers, but will error out on all other text values that are non-numeric.
The unary form (suggested by Dave), will by-pass text *AND* numeric text, 
and just calculate "true" numbers.

So, if your calculations are different using Dave's suggestion, I would 
guess that you have *both* numeric and text numbers present in your 
datalist.

If you would/could revise your formula to:

=IF(I26="",0,I26-H26)

then you could continue using the asterisk form, since zero would replace 
the text null ( "" ).

You might try it and post back with results.
-- 
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"JP" <JohnP26@msn.com> wrote in message 
news:7n8do3duhaec4llvhueelurr7kg6dc10ea@4ax.com...
> OK, checked all of that out.  This is the formula I have in the fist
> cell.
>
> =IF(I26="","",I26-H26)
>
> I drag it down the column.  For every new day that data is entered I
> skip a space.  I couldn't find the three cells.
>
>
>
> On Thu, 10 Jan 2008 16:06:06 -0600, Dave Peterson
> <petersod@verizonXSPAM.net> wrote:
>
>>That means that there are 3 values that are not numbers.
>>
>>=counta() returns the number of cells with something in them.
>>=count() returns the number of cells with numbers in them.
>>
>>Select the range
>>edit|goto|special
>>and look for numbers or text or blanks
>>
>>Look in values or formulas or both.
>>
>>JP wrote:
>>>
>>> 221, and 118 respectively.
>>>
>>> On Thu, 10 Jan 2008 08:41:57 -0600, Dave Peterson
>>> <petersod@verizonXSPAM.net> wrote:
>>>
>>> >=counta(m25:m2000)
> 


0
ragdyer1 (4060)
1/11/2008 1:34:12 AM
Not sure I understand all of it, but it worked.  CountA and Count both
return 289, so I guess that did the trick.  The only problem now is
that $0.00 shows up in the skipped rows, so I guess I'll have to use
condiditional formatting to get rid of them.

Appreciate the advice.  Thanks a lot.

On Thu, 10 Jan 2008 17:34:12 -0800, "RagDyer" <ragdyer@cutoutmsn.com>
wrote:

>You *should not* use the asterisk form of Sumproduct() when you have your 
>calculating column(s) populated by formulas that may return zero length 
>strings (nulls - "" ), as you *do* have!
>These returns are considered TEXT, and will generate the #Value! error when 
>Column I is empty, causing a null ( "" ) return.in Column M.
>
>The asterisk form will calculate text numbers, as long as they look like 
>numbers, but will error out on all other text values that are non-numeric.
>The unary form (suggested by Dave), will by-pass text *AND* numeric text, 
>and just calculate "true" numbers.
>
>So, if your calculations are different using Dave's suggestion, I would 
>guess that you have *both* numeric and text numbers present in your 
>datalist.
>
>If you would/could revise your formula to:
>
>=IF(I26="",0,I26-H26)
>
>then you could continue using the asterisk form, since zero would replace 
>the text null ( "" ).
>
>You might try it and post back with results.

0
JohnP26 (33)
1/11/2008 3:43:06 PM
Found a gliltch.  Now this formula in a column two columns over no
longer works.  

=IF(M28="","",IF(M29="",SUM(M$26:M28)-SUM(O$26:O27),""))

On Fri, 11 Jan 2008 07:43:06 -0800, JP <JohnP26@msn.com> wrote:

>Not sure I understand all of it, but it worked.  CountA and Count both
>return 289, so I guess that did the trick.  The only problem now is
>that $0.00 shows up in the skipped rows, so I guess I'll have to use
>condiditional formatting to get rid of them.
>
>Appreciate the advice.  Thanks a lot.
>
>On Thu, 10 Jan 2008 17:34:12 -0800, "RagDyer" <ragdyer@cutoutmsn.com>
>wrote:
>
>>You *should not* use the asterisk form of Sumproduct() when you have your 
>>calculating column(s) populated by formulas that may return zero length 
>>strings (nulls - "" ), as you *do* have!
>>These returns are considered TEXT, and will generate the #Value! error when 
>>Column I is empty, causing a null ( "" ) return.in Column M.
>>
>>The asterisk form will calculate text numbers, as long as they look like 
>>numbers, but will error out on all other text values that are non-numeric.
>>The unary form (suggested by Dave), will by-pass text *AND* numeric text, 
>>and just calculate "true" numbers.
>>
>>So, if your calculations are different using Dave's suggestion, I would 
>>guess that you have *both* numeric and text numbers present in your 
>>datalist.
>>
>>If you would/could revise your formula to:
>>
>>=IF(I26="",0,I26-H26)
>>
>>then you could continue using the asterisk form, since zero would replace 
>>the text null ( "" ).
>>
>>You might try it and post back with results.

0
JohnP26 (33)
1/11/2008 7:54:54 PM
Look, if data is to be keyed in, or imported in, the asterisk form of 
Sumproduct is the way to go ... warns you of mistakes ... BUT ... if you're 
populating your calculating columns with formulas returning nulls, you 
*should* be using the unary form, as Dave suggested.

Now that you've revised your formula to return zeroes, compare both forms of 
the function and see if they both return the same result.

If they don't, that means that you have true numbers, and text that looks 
like numbers, in your datalist.
That's not a good thing to have happening, at any time!

You can use a 'helper" column, and reference it to any questionable columns, 
to see if all your numbers are numeric numbers.

Simply use this  in any vacant column:

=Isnumber(M26)
and drag it down to see if every row returns a TRUE.

If you see a FALSE, that's the row(s) to fix!

On the other hand, if you insist on using the asterisk form, and you're 
going to use CF to fix the display, then you'll have to revise *all* your 
other formulas to match the revised value of your calculating columns.

FROM:
=IF(M28="","",IF(M29="",SUM(M$26:M28)-SUM(O$26:O27),""))

TO:
=IF(M28=0,0,IF(M29=0,SUM(M$26:M28)-SUM(O$26:O27),0))

It *may* become a nightmare trying to change everything around.

Your choice!
-- 
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

"JP" <JohnP26@msn.com> wrote in message 
news:caifo3l1rrjkj4rjf7ldjdh8d6ctdghul8@4ax.com...
> Found a gliltch.  Now this formula in a column two columns over no
> longer works.
>
> =IF(M28="","",IF(M29="",SUM(M$26:M28)-SUM(O$26:O27),""))
>
> On Fri, 11 Jan 2008 07:43:06 -0800, JP <JohnP26@msn.com> wrote:
>
>>Not sure I understand all of it, but it worked.  CountA and Count both
>>return 289, so I guess that did the trick.  The only problem now is
>>that $0.00 shows up in the skipped rows, so I guess I'll have to use
>>condiditional formatting to get rid of them.
>>
>>Appreciate the advice.  Thanks a lot.
>>
>>On Thu, 10 Jan 2008 17:34:12 -0800, "RagDyer" <ragdyer@cutoutmsn.com>
>>wrote:
>>
>>>You *should not* use the asterisk form of Sumproduct() when you have your
>>>calculating column(s) populated by formulas that may return zero length
>>>strings (nulls - "" ), as you *do* have!
>>>These returns are considered TEXT, and will generate the #Value! error 
>>>when
>>>Column I is empty, causing a null ( "" ) return.in Column M.
>>>
>>>The asterisk form will calculate text numbers, as long as they look like
>>>numbers, but will error out on all other text values that are 
>>>non-numeric.
>>>The unary form (suggested by Dave), will by-pass text *AND* numeric text,
>>>and just calculate "true" numbers.
>>>
>>>So, if your calculations are different using Dave's suggestion, I would
>>>guess that you have *both* numeric and text numbers present in your
>>>datalist.
>>>
>>>If you would/could revise your formula to:
>>>
>>>=IF(I26="",0,I26-H26)
>>>
>>>then you could continue using the asterisk form, since zero would replace
>>>the text null ( "" ).
>>>
>>>You might try it and post back with results.
> 


0
ragdyer1 (4060)
1/11/2008 8:24:02 PM
I had to change the formula in three columns as you suggested to make
everything work.  Thanks for your help.

On Fri, 11 Jan 2008 12:24:02 -0800, "RagDyer" <ragdyer@cutoutmsn.com>
wrote:

>Look, if data is to be keyed in, or imported in, the asterisk form of 
>Sumproduct is the way to go ... warns you of mistakes ... BUT ... if you're 
>populating your calculating columns with formulas returning nulls, you 
>*should* be using the unary form, as Dave suggested.
>
>Now that you've revised your formula to return zeroes, compare both forms of 
>the function and see if they both return the same result.
>
>If they don't, that means that you have true numbers, and text that looks 
>like numbers, in your datalist.
>That's not a good thing to have happening, at any time!
>
>You can use a 'helper" column, and reference it to any questionable columns, 
>to see if all your numbers are numeric numbers.
>
>Simply use this  in any vacant column:
>
>=Isnumber(M26)
>and drag it down to see if every row returns a TRUE.
>
>If you see a FALSE, that's the row(s) to fix!
>
>On the other hand, if you insist on using the asterisk form, and you're 
>going to use CF to fix the display, then you'll have to revise *all* your 
>other formulas to match the revised value of your calculating columns.
>
>FROM:
>=IF(M28="","",IF(M29="",SUM(M$26:M28)-SUM(O$26:O27),""))
>
>TO:
>=IF(M28=0,0,IF(M29=0,SUM(M$26:M28)-SUM(O$26:O27),0))
>
>It *may* become a nightmare trying to change everything around.
>
>Your choice!

0
JohnP26 (33)
1/11/2008 11:37:22 PM
You're very welcome, and thank you for the feed-back.
-- 
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"JP" <JohnP26@msn.com> wrote in message 
news:ocvfo3h70u8qsd3i5mrugsant6o35029ut@4ax.com...
>I had to change the formula in three columns as you suggested to make
> everything work.  Thanks for your help.
>
> On Fri, 11 Jan 2008 12:24:02 -0800, "RagDyer" <ragdyer@cutoutmsn.com>
> wrote:
>
>>Look, if data is to be keyed in, or imported in, the asterisk form of
>>Sumproduct is the way to go ... warns you of mistakes ... BUT ... if 
>>you're
>>populating your calculating columns with formulas returning nulls, you
>>*should* be using the unary form, as Dave suggested.
>>
>>Now that you've revised your formula to return zeroes, compare both forms 
>>of
>>the function and see if they both return the same result.
>>
>>If they don't, that means that you have true numbers, and text that looks
>>like numbers, in your datalist.
>>That's not a good thing to have happening, at any time!
>>
>>You can use a 'helper" column, and reference it to any questionable 
>>columns,
>>to see if all your numbers are numeric numbers.
>>
>>Simply use this  in any vacant column:
>>
>>=Isnumber(M26)
>>and drag it down to see if every row returns a TRUE.
>>
>>If you see a FALSE, that's the row(s) to fix!
>>
>>On the other hand, if you insist on using the asterisk form, and you're
>>going to use CF to fix the display, then you'll have to revise *all* your
>>other formulas to match the revised value of your calculating columns.
>>
>>FROM:
>>=IF(M28="","",IF(M29="",SUM(M$26:M28)-SUM(O$26:O27),""))
>>
>>TO:
>>=IF(M28=0,0,IF(M29=0,SUM(M$26:M28)-SUM(O$26:O27),0))
>>
>>It *may* become a nightmare trying to change everything around.
>>
>>Your choice!
> 


0
ragdyer1 (4060)
1/12/2008 12:34:02 AM
Reply:

Similar Artilces:

Solver Question!
Hi! I have a question about solver. I will try to make this as clear as possible. I am trying to use the solver in Excel to find the smallest amount of interest accrued over time. Let me explain. I have 2 accounts. Let’s say one has a balance of $5000, and the other a balance of $10000. I have annual interest rates for both of them (5% and 2% respectively). I have to make a minimum monthly $100 payment on account 1 and $150 on account 2. I have a total of $400 per month to spend on payments. I have set up a way using CUMIPMT to calculate the total interest paid on each account for the durat...

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

ComboBox Question
I'm doing a project which requires that I change a listbox to a dropdown (style 1) combobox so the user can enter a part number. The combobox is populated with anywhere from 1 to 10 part numbers. If there is only one part number, I want the code to automatically select it. The following code is OK for a listbox, but what is the equivilent for a combobox? If partCount = 1 Then lstPartNo.Selected(0) = True End If Thanks in advance for any help on this. John __________ Information from ESET Smart Security, version of virus signature database 4935 (20100311) ______...

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

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

How to get an reply to your question
The web forum that interfaces with the Office newsgroup (NNTP) has been broken since Sept. The people that volunteer their time to answer questions do not use the Mactopia forum and can't see your messages. If you use a newsreader rather than the Mactopia interface to this newsgroup you can download old messages and search for answers or start a new question. If you are not familiar with a newsreader, Entourage's basic newsreader already has the Microsoft News server included. All you do is subscribe to the newsgroups you want. See this page for help: <http://www.entourage.mvps.o...

general questions.. please help
can i know normally do people convert text files to xml format? how can we do to convert data from text files to xml? is it possible? to generate xml from jsp is it done automatically or manually? thanks in advance! appreciate if there are any links helpful... thanks all of u! jeremy *-----------------------* Posted at: www.GroupSrv.com *-----------------------* Hello Jeremy, > can i know normally do people convert text files to xml format? not normally by hand, but it can be done. Doing it with a program is better. > how can we do to convert data from text files to x...

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

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

Macro Questions:
Hi there Hopefully these questions wont sound too primitive: a) I've recorded a very simple macro that copies a row of date (C10 to O10) and pastes (paste special - values only) into a master work book. The entry point in the master work book is the current active cell; each new row of data goes into a new row (B4, B5, B6 etc...) slowly building a table. My question is how do I make the macro end with the active cell in the next row in B column - so I can run the same macro again on the next spreadsheet? b) How wise is it to use the 'save' command in a macro? c) How do I ke...

Knowledge Base Question
It is possible to allow customers to the knowledge base articles stored in CRM? Preferably via Web services? Also, how does a customer support ticket get entered into CRM - via email or could it be via Webservices as well? c360 has a Customer portal that facilitates this however it is expensive. You could write one via the SDK. With regard to customer support ticket, we use the c360 email to case add-on. Sean "Paul Sullivan" <Paul Sullivan@discussions.microsoft.com> wrote in message news:5040ACA4-EDF1-45DE-965D-560381B047E9@microsoft.com... > It is possible to allo...

DHCP question
Hi, I have 2 IP which I want to exclude from a range. One of the IP is used by a switch and the other one is assigned to a phone system - so I dont know the mac address. How do I exclude those 2 Ip's from the range. I only see the option to reserve in DHCP in window 2003. Thanks biren wrote: > Hi, > > I have 2 IP which I want to exclude from a range. One of the IP is > used by a switch and the other one is assigned to a phone system - so > I dont know the mac address. > > How do I exclude those 2 Ip's from the range. I only see the option to...

Windows xp updates question
When I try to update Windows XP IE will not load page. It's as if the page didn't exist. I go click on 'Tools/Window Update' but the MS update page will not be found. How can I fix this? Tony Alpha wrote: > When I try to update Windows XP IE will not load page. It's as if > the page didn't exist. I go click on 'Tools/Window Update' but the > MS update page will not be found. > > How can I fix this? Windows XP _____ Edition with Service Pack _ __-Bit? Internet Explorer version _? Assuming some 32-bit version of Windows XP...

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

simple drawing question
I think my problem is that I don't know what device context to use for the Rectangle function. I made a new function in my view class. I tried CClientDC dc(this), but that didn't seem to work. The OnDraw(CDC* pDC) function has the device context passed to it, which makes drawing in that function easy. I know this is probably a stupid question, but I guess that makes answering it that much easier. Thanks for you help -- to reply, remove Big10 from address "Andrew Sasak" <sasakand@Big10.msu.edu> wrote in message news:uQi6hqy7DHA.2676@TK2MSFTNGP10.phx.gbl... > I t...

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

Active Directory Connector Question
Hi All, I currently have an ADC connection that is one way from Exchange 5.5 to AD. I have been having trouble with this setup as there are some accounts on the 5.5 side that did not get stamped with the ADC Global Names attribute, and they keep trying to replicate back in and will cause problems with distribution lists and whatnot. So my question is: Can I get rid of this connector and make a two way connection without any problems? If I do this, will there be any side effects like disabled accounts being created for user accounts that already have mailboxes? We have already migrated all...

output question
#define the properties to be returned ($colPropList is an array) $colProplist = "name","member" foreach ($i in $colPropList) { $Searcher.PropertiesToLoad.Add($i) } Above is the code to set the properties for a directory search but it produces a 0 1 in the outputs. Any one happen to have experienced this and how to get rid of the 0 1 output ? Thanks Redirect result of the method to $null { $Searcher.PropertiesToLoad.Add($i) > $null } -Paul "Chris" wrote: > #define the properties to be returned ($colPropList is an array) > $col...

Rule Question using From name
I get these annoying spam emails for prescription drugs. They are always From: "Doctor" with the actual email address being different each time. ex. Doctor [betacomp@griggsgroup.com] There's no point in adding these emails to my Junk Senders list because the address changes everytime. The one thing they all have in common though, is the From: name - Doctor. So, how do I tell Outlook to delete all emails from "Doctor" ? I manually added, to my junk senders list, just the word - Doctor, but it does not delete these emails. If I try to use one of the rules templates,...

sumproduct
I have this formula in use and it works perfectly. I now need to change the sum area from H4:h484 to H4:J484 and now it only give me 0. How do I get it to sum the larger area?? =IF($B3<=0,"",SUMPRODUCT((SVEexp!$B$4:$B$484=Summary!$B3)*(SVEexp!$C$4:$C$48 4=Summary!$C3),SVEexp!$H$4:$H$484)) Thank you, Jerry Hi You need to make sure that all of the ranges in SUMPRODUCT are the same size. If you alter one of the ranges, you'll have to alter the others accordingly. Hope this helps. Andy. "Jerry Kinder" <jkinder@jkinder.com> wrote in message news:eeznF54...

Addressbook question
Hi I recently shared my Outlook address book with OE using the registry change "UseOutlook". I have found that contacts added thereafter are not being delivered to my Inbox (in Outlook Express). Can you please advise me, and possibly let me know a solution to fix. Thanks again in advance for your assistance. Julia Wouldn't they show up in the OE's Address Book (WAB.EXE) and not OE's Inbox? "Julia" <noreply@mail.com> wrote in message news:Ok0rY17pFHA.3160@TK2MSFTNGP14.phx.gbl... > Hi > > I recently shared my Outlook address book with OE...

formula question #27
I have an array of numbers like 100 0.01 200 0.015 300 0.02 400 0.025 500 0.03 say the range name is tab1 and an array of numbers like (call it tab2) 150 250 350 I want to look up the numbers from tab2 in tab1 and multilply it by the 2nd column from tab1 and add up the result. So, I want 150*.01+250*.015+350*.02 I tried as an array formula =sum((vlookup(tab2,tab1,2)*tab2) but this does not work. What should I be doing? TIA Say your tab2 info in col. A. In col. B type vlookup(A1,tab1!$A$1:$B$5,2,TRUE) Note: Use "true" in the vlookup, since your values in tab1 col. A and ...

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

budget/accounts usage question
Hello, In my budget, I can make an entry for my car loan under the Debt category, but how do I budget for monthly credit card payments, or payments (transfers) to other liability accounts? It only lets me add categories, not transfers. Should I set my accounts up differently? Thanks, -Paul ...