software

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($B3lt;=0,quot;quot;,SUMPRODUCT((SVEexp!$B$4:$B$484=Summa ry!$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.

quot;Jerry Kinderquot; gt; wrote in message
...
gt;I have this formula in use and it works perfectly. I now need to change
gt;the
gt; sum area from H4:h484 to H4:J484 and now it only give me 0. How do I get
gt; it
gt; to sum the larger area??
gt;
gt; =IF($B3lt;=0,quot;quot;,SUMPRODUCT((SVEexp!$B$4:$B$484=Summa ry!$B3)*(SVEexp!$C$4:$C$48
gt; 4=Summary!$C3),SVEexp!$H$4:$H$484))
gt;
gt; Thank you, Jerry
gt;
gt;
I changed the end range to
=IF($B3lt;=0,quot;quot;,SUMPRODUCT((SVEexp!$B$4:$B$484=Summa ry!$B3)*(SVEexp!$C$4:$C$48
4=Summary!$C3),SVEexp!$H$4:$J$484)) and got #Value error. The only change
was the end range where the numbers are.
lt;Andygt; wrote in message ...
gt; Hi
gt;
gt; You need to make sure that all of the ranges in SUMPRODUCT are the same
gt; size. If you alter one of the ranges, you'll have to alter the others
gt; accordingly.
gt;
gt; Hope this helps.
gt; Andy.
gt;
gt; quot;Jerry Kinderquot; gt; wrote in message
gt; ...
gt; gt;I have this formula in use and it works perfectly. I now need to change
gt; gt;the
gt; gt; sum area from H4:h484 to H4:J484 and now it only give me 0. How do I
get
gt; gt; it
gt; gt; to sum the larger area??
gt; gt;
gt; gt;
=IF($B3lt;=0,quot;quot;,SUMPRODUCT((SVEexp!$B$4:$B$484=Summa ry!$B3)*(SVEexp!$C$4:$C$48
gt; gt; 4=Summary!$C3),SVEexp!$H$4:$H$484))
gt; gt;
gt; gt; Thank you, Jerry
gt; gt;
gt; gt;
gt;
gt;
But like I said, all of the ranges within a SUMPRODUCT must be the same
size. You've doubled the size of the last range so you'll have to do the
same with the others.
Post some sample data if I'm not understanding you correctly.

Andy.

quot;Jerry Kinderquot; gt; wrote in message
...
gt;I changed the end range to
gt; =IF($B3lt;=0,quot;quot;,SUMPRODUCT((SVEexp!$B$4:$B$484=Summa ry!$B3)*(SVEexp!$C$4:$C$48
gt; 4=Summary!$C3),SVEexp!$H$4:$J$484)) and got #Value error. The only change
gt; was the end range where the numbers are.
gt;
gt;
gt;
gt; lt;Andygt; wrote in message ...
gt;gt; Hi
gt;gt;
gt;gt; You need to make sure that all of the ranges in SUMPRODUCT are the same
gt;gt; size. If you alter one of the ranges, you'll have to alter the others
gt;gt; accordingly.
gt;gt;
gt;gt; Hope this helps.
gt;gt; Andy.
gt;gt;
gt;gt; quot;Jerry Kinderquot; gt; wrote in message
gt;gt; ...
gt;gt; gt;I have this formula in use and it works perfectly. I now need to change
gt;gt; gt;the
gt;gt; gt; sum area from H4:h484 to H4:J484 and now it only give me 0. How do I
gt; get
gt;gt; gt; it
gt;gt; gt; to sum the larger area??
gt;gt; gt;
gt;gt; gt;
gt; =IF($B3lt;=0,quot;quot;,SUMPRODUCT((SVEexp!$B$4:$B$484=Summa ry!$B3)*(SVEexp!$C$4:$C$48
gt;gt; gt; 4=Summary!$C3),SVEexp!$H$4:$H$484))
gt;gt; gt;
gt;gt; gt; Thank you, Jerry
gt;gt; gt;
gt;gt; gt;
gt;gt;
gt;gt;
gt;
gt;
Hi,
I hope this helps clear up what I am asking.
This is the data sheet all:
B C F G
H
Checks Cash
Visa
Office Phone 24.90
Wages Sue 200.00
Office Misc. 37.34
Wages Bill 200.00
Office Supplies
12.96

Where the formula is quot;the summary sheetquot;
B C D
Office Phone t he formula for totals
Office Misc.
Offcie Supplies
Wages Sue
Wages Bill
lt;Andygt; wrote in message ...
gt; But like I said, all of the ranges within a SUMPRODUCT must be the same
gt; size. You've doubled the size of the last range so you'll have to do the
gt; same with the others.
gt; Post some sample data if I'm not understanding you correctly.
gt;
gt; Andy.
gt;
gt; quot;Jerry Kinderquot; gt; wrote in message
gt; ...
gt; gt;I changed the end range to
gt; gt;
=IF($B3lt;=0,quot;quot;,SUMPRODUCT((SVEexp!$B$4:$B$484=Summa ry!$B3)*(SVEexp!$C$4:$C$48
gt; gt; 4=Summary!$C3),SVEexp!$H$4:$J$484)) and got #Value error. The only
change
gt; gt; was the end range where the numbers are.
gt; gt;
gt; gt;
gt; gt;
gt; gt; lt;Andygt; wrote in message
...
gt; gt;gt; Hi
gt; gt;gt;
gt; gt;gt; You need to make sure that all of the ranges in SUMPRODUCT are the same
gt; gt;gt; size. If you alter one of the ranges, you'll have to alter the others
gt; gt;gt; accordingly.
gt; gt;gt;
gt; gt;gt; Hope this helps.
gt; gt;gt; Andy.
gt; gt;gt;
gt; gt;gt; quot;Jerry Kinderquot; gt; wrote in message
gt; gt;gt; ...
gt; gt;gt; gt;I have this formula in use and it works perfectly. I now need to
change
gt; gt;gt; gt;the
gt; gt;gt; gt; sum area from H4:h484 to H4:J484 and now it only give me 0. How do I
gt; gt; get
gt; gt;gt; gt; it
gt; gt;gt; gt; to sum the larger area??
gt; gt;gt; gt;
gt; gt;gt; gt;
gt; gt;
=IF($B3lt;=0,quot;quot;,SUMPRODUCT((SVEexp!$B$4:$B$484=Summa ry!$B3)*(SVEexp!$C$4:$C$48
gt; gt;gt; gt; 4=Summary!$C3),SVEexp!$H$4:$H$484))
gt; gt;gt; gt;
gt; gt;gt; gt; Thank you, Jerry
gt; gt;gt; gt;
gt; gt;gt; gt;
gt; gt;gt;
gt; gt;gt;
gt; gt;
gt; gt;
gt;
gt;
Jerry,

Try

=IF($B3lt;=0,quot;quot;,SUMPRODUCT((SVEexp!$B$4:$B$484=Summa ry!$B3)*(SVEexp!$C$4:$C$48
4=Summary!$C3)*(SVEexp!$H$4:$J$484))

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

quot;Jerry Kinderquot; gt; wrote in message
...
gt; Hi,
gt; I hope this helps clear up what I am asking.
gt; This is the data sheet all:
gt; B C F G
gt; H
gt; Checks Cash
gt; Visa
gt; Office Phone 24.90
gt; Wages Sue 200.00
gt; Office Misc. 37.34
gt; Wages Bill 200.00
gt; Office Supplies
gt; 12.96
gt;
gt; Where the formula is quot;the summary sheetquot;
gt; B C D
gt; Office Phone t he formula for totals
gt; Office Misc.
gt; Offcie Supplies
gt; Wages Sue
gt; Wages Bill
gt;
gt;
gt;
gt; lt;Andygt; wrote in message ...
gt; gt; But like I said, all of the ranges within a SUMPRODUCT must be the same
gt; gt; size. You've doubled the size of the last range so you'll have to do the
gt; gt; same with the others.
gt; gt; Post some sample data if I'm not understanding you correctly.
gt; gt;
gt; gt; Andy.
gt; gt;
gt; gt; quot;Jerry Kinderquot; gt; wrote in message
gt; gt; ...
gt; gt; gt;I changed the end range to
gt; gt; gt;
gt;
=IF($B3lt;=0,quot;quot;,SUMPRODUCT((SVEexp!$B$4:$B$484=Summa ry!$B3)*(SVEexp!$C$4:$C$48
gt; gt; gt; 4=Summary!$C3),SVEexp!$H$4:$J$484)) and got #Value error. The only
gt; change
gt; gt; gt; was the end range where the numbers are.
gt; gt; gt;
gt; gt; gt;
gt; gt; gt;
gt; gt; gt; lt;Andygt; wrote in message
gt; ...
gt; gt; gt;gt; Hi
gt; gt; gt;gt;
gt; gt; gt;gt; You need to make sure that all of the ranges in SUMPRODUCT are the
same
gt; gt; gt;gt; size. If you alter one of the ranges, you'll have to alter the others
gt; gt; gt;gt; accordingly.
gt; gt; gt;gt;
gt; gt; gt;gt; Hope this helps.
gt; gt; gt;gt; Andy.
gt; gt; gt;gt;
gt; gt; gt;gt; quot;Jerry Kinderquot; gt; wrote in message
gt; gt; gt;gt; ...
gt; gt; gt;gt; gt;I have this formula in use and it works perfectly. I now need to
gt; change
gt; gt; gt;gt; gt;the
gt; gt; gt;gt; gt; sum area from H4:h484 to H4:J484 and now it only give me 0. How do
I
gt; gt; gt; get
gt; gt; gt;gt; gt; it
gt; gt; gt;gt; gt; to sum the larger area??
gt; gt; gt;gt; gt;
gt; gt; gt;gt; gt;
gt; gt; gt;
gt;
=IF($B3lt;=0,quot;quot;,SUMPRODUCT((SVEexp!$B$4:$B$484=Summa ry!$B3)*(SVEexp!$C$4:$C$48
gt; gt; gt;gt; gt; 4=Summary!$C3),SVEexp!$H$4:$H$484))
gt; gt; gt;gt; gt;
gt; gt; gt;gt; gt; Thank you, Jerry
gt; gt; gt;gt; gt;
gt; gt; gt;gt; gt;
gt; gt; gt;gt;
gt; gt; gt;gt;
gt; gt; gt;
gt; gt; gt;
gt; gt;
gt; gt;
gt;
gt;
YES!!!! it is PERFECT.

Thank you, Thank you, Thank you, Thank youquot;Bob Phillipsquot; gt; wrote in message
...
gt; Jerry,
gt;
gt; Try
gt;
gt;
=IF($B3lt;=0,quot;quot;,SUMPRODUCT((SVEexp!$B$4:$B$484=Summa ry!$B3)*(SVEexp!$C$4:$C$48
gt; 4=Summary!$C3)*(SVEexp!$H$4:$J$484))
gt;
gt; --
gt; HTH
gt;
gt; Bob Phillips
gt;
gt; (remove nothere from email address if mailing direct)
gt;
gt; quot;Jerry Kinderquot; gt; wrote in message
gt; ...
gt; gt; Hi,
gt; gt; I hope this helps clear up what I am asking.
gt; gt; This is the data sheet all:
gt; gt; B C F G
gt; gt; H
gt; gt; Checks Cash
gt; gt; Visa
gt; gt; Office Phone 24.90
gt; gt; Wages Sue 200.00
gt; gt; Office Misc. 37.34
gt; gt; Wages Bill 200.00
gt; gt; Office Supplies
gt; gt; 12.96
gt; gt;
gt; gt; Where the formula is quot;the summary sheetquot;
gt; gt; B C D
gt; gt; Office Phone t he formula for totals
gt; gt; Office Misc.
gt; gt; Offcie Supplies
gt; gt; Wages Sue
gt; gt; Wages Bill
gt; gt;
gt; gt;
gt; gt;
gt; gt; lt;Andygt; wrote in message ...
gt; gt; gt; But like I said, all of the ranges within a SUMPRODUCT must be the
same
gt; gt; gt; size. You've doubled the size of the last range so you'll have to do
the
gt; gt; gt; same with the others.
gt; gt; gt; Post some sample data if I'm not understanding you correctly.
gt; gt; gt;
gt; gt; gt; Andy.
gt; gt; gt;
gt; gt; gt; quot;Jerry Kinderquot; gt; wrote in message
gt; gt; gt; ...
gt; gt; gt; gt;I changed the end range to
gt; gt; gt; gt;
gt; gt;
gt;
=IF($B3lt;=0,quot;quot;,SUMPRODUCT((SVEexp!$B$4:$B$484=Summa ry!$B3)*(SVEexp!$C$4:$C$48
gt; gt; gt; gt; 4=Summary!$C3),SVEexp!$H$4:$J$484)) and got #Value error. The only
gt; gt; change
gt; gt; gt; gt; was the end range where the numbers are.
gt; gt; gt; gt;
gt; gt; gt; gt;
gt; gt; gt; gt;
gt; gt; gt; gt; lt;Andygt; wrote in message
gt; gt; ...
gt; gt; gt; gt;gt; Hi
gt; gt; gt; gt;gt;
gt; gt; gt; gt;gt; You need to make sure that all of the ranges in SUMPRODUCT are the
gt; same
gt; gt; gt; gt;gt; size. If you alter one of the ranges, you'll have to alter the
others
gt; gt; gt; gt;gt; accordingly.
gt; gt; gt; gt;gt;
gt; gt; gt; gt;gt; Hope this helps.
gt; gt; gt; gt;gt; Andy.
gt; gt; gt; gt;gt;
gt; gt; gt; gt;gt; quot;Jerry Kinderquot; gt; wrote in message
gt; gt; gt; gt;gt; ...
gt; gt; gt; gt;gt; gt;I have this formula in use and it works perfectly. I now need to
gt; gt; change
gt; gt; gt; gt;gt; gt;the
gt; gt; gt; gt;gt; gt; sum area from H4:h484 to H4:J484 and now it only give me 0. How
do
gt; I
gt; gt; gt; gt; get
gt; gt; gt; gt;gt; gt; it
gt; gt; gt; gt;gt; gt; to sum the larger area??
gt; gt; gt; gt;gt; gt;
gt; gt; gt; gt;gt; gt;
gt; gt; gt; gt;
gt; gt;
gt;
=IF($B3lt;=0,quot;quot;,SUMPRODUCT((SVEexp!$B$4:$B$484=Summa ry!$B3)*(SVEexp!$C$4:$C$48
gt; gt; gt; gt;gt; gt; 4=Summary!$C3),SVEexp!$H$4:$H$484))
gt; gt; gt; gt;gt; gt;
gt; gt; gt; gt;gt; gt; Thank you, Jerry
gt; gt; gt; gt;gt; gt;
gt; gt; gt; gt;gt; gt;
gt; gt; gt; gt;gt;
gt; gt; gt; gt;gt;
gt; gt; gt; gt;
gt; gt; gt; gt;
gt; gt; gt;
gt; gt; gt;
gt; gt;
gt; gt;
gt;
gt;
It's a pleasure (4 times) lt;vbggt;

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

quot;Jerry Kinderquot; gt; wrote in message
...
gt; YES!!!! it is PERFECT.
gt;
gt; Thank you, Thank you, Thank you, Thank you
gt;
gt;
gt; quot;Bob Phillipsquot; gt; wrote in message
gt; ...
gt; gt; Jerry,
gt; gt;
gt; gt; Try
gt; gt;
gt; gt;
gt;
=IF($B3lt;=0,quot;quot;,SUMPRODUCT((SVEexp!$B$4:$B$484=Summa ry!$B3)*(SVEexp!$C$4:$C$48
gt; gt; 4=Summary!$C3)*(SVEexp!$H$4:$J$484))
gt; gt;
gt; gt; --
gt; gt; HTH
gt; gt;
gt; gt; Bob Phillips
gt; gt;
gt; gt; (remove nothere from email address if mailing direct)
gt; gt;
gt; gt; quot;Jerry Kinderquot; gt; wrote in message
gt; gt; ...
gt; gt; gt; Hi,
gt; gt; gt; I hope this helps clear up what I am asking.
gt; gt; gt; This is the data sheet all:
gt; gt; gt; B C F G
gt; gt; gt; H
gt; gt; gt; Checks
Cash
gt; gt; gt; Visa
gt; gt; gt; Office Phone 24.90
gt; gt; gt; Wages Sue 200.00
gt; gt; gt; Office Misc. 37.34
gt; gt; gt; Wages Bill 200.00
gt; gt; gt; Office Supplies
gt; gt; gt; 12.96
gt; gt; gt;
gt; gt; gt; Where the formula is quot;the summary sheetquot;
gt; gt; gt; B C D
gt; gt; gt; Office Phone t he formula for totals
gt; gt; gt; Office Misc.
gt; gt; gt; Offcie Supplies
gt; gt; gt; Wages Sue
gt; gt; gt; Wages Bill
gt; gt; gt;
gt; gt; gt;
gt; gt; gt;
gt; gt; gt; lt;Andygt; wrote in message ...
gt; gt; gt; gt; But like I said, all of the ranges within a SUMPRODUCT must be the
gt; same
gt; gt; gt; gt; size. You've doubled the size of the last range so you'll have to do
gt; the
gt; gt; gt; gt; same with the others.
gt; gt; gt; gt; Post some sample data if I'm not understanding you correctly.
gt; gt; gt; gt;
gt; gt; gt; gt; Andy.
gt; gt; gt; gt;
gt; gt; gt; gt; quot;Jerry Kinderquot; gt; wrote in message
gt; gt; gt; gt; ...
gt; gt; gt; gt; gt;I changed the end range to
gt; gt; gt; gt; gt;
gt; gt; gt;
gt; gt;
gt;
=IF($B3lt;=0,quot;quot;,SUMPRODUCT((SVEexp!$B$4:$B$484=Summa ry!$B3)*(SVEexp!$C$4:$C$48
gt; gt; gt; gt; gt; 4=Summary!$C3),SVEexp!$H$4:$J$484)) and got #Value error. The only
gt; gt; gt; change
gt; gt; gt; gt; gt; was the end range where the numbers are.
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; lt;Andygt; wrote in message
gt; gt; gt; ...
gt; gt; gt; gt; gt;gt; Hi
gt; gt; gt; gt; gt;gt;
gt; gt; gt; gt; gt;gt; You need to make sure that all of the ranges in SUMPRODUCT are
the
gt; gt; same
gt; gt; gt; gt; gt;gt; size. If you alter one of the ranges, you'll have to alter the
gt; others
gt; gt; gt; gt; gt;gt; accordingly.
gt; gt; gt; gt; gt;gt;
gt; gt; gt; gt; gt;gt; Hope this helps.
gt; gt; gt; gt; gt;gt; Andy.
gt; gt; gt; gt; gt;gt;
gt; gt; gt; gt; gt;gt; quot;Jerry Kinderquot; gt; wrote in message
gt; gt; gt; gt; gt;gt; ...
gt; gt; gt; gt; gt;gt; gt;I have this formula in use and it works perfectly. I now need
to
gt; gt; gt; change
gt; gt; gt; gt; gt;gt; gt;the
gt; gt; gt; gt; gt;gt; gt; sum area from H4:h484 to H4:J484 and now it only give me 0.
How
gt; do
gt; gt; I
gt; gt; gt; gt; gt; get
gt; gt; gt; gt; gt;gt; gt; it
gt; gt; gt; gt; gt;gt; gt; to sum the larger area??
gt; gt; gt; gt; gt;gt; gt;
gt; gt; gt; gt; gt;gt; gt;
gt; gt; gt; gt; gt;
gt; gt; gt;
gt; gt;
gt;
=IF($B3lt;=0,quot;quot;,SUMPRODUCT((SVEexp!$B$4:$B$484=Summa ry!$B3)*(SVEexp!$C$4:$C$48
gt; gt; gt; gt; gt;gt; gt; 4=Summary!$C3),SVEexp!$H$4:$H$484))
gt; gt; gt; gt; gt;gt; gt;
gt; gt; gt; gt; gt;gt; gt; Thank you, Jerry
gt; gt; gt; gt; gt;gt; gt;
gt; gt; gt; gt; gt;gt; gt;
gt; gt; gt; gt; gt;gt;
gt; gt; gt; gt; gt;gt;
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt;
gt; gt; gt; gt;
gt; gt; gt; gt;
gt; gt; gt;
gt; gt; gt;
gt; gt;
gt; gt;
gt;
gt;