| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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=Summa ry!$B3)*(SVEexp!$C$4:$C$48 4=Summary!$C3),SVEexp!$H$4:$H$484)) Thank you, Jerry |
|
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
sumproduct
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" wrote in message ... 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=Summa ry!$B3)*(SVEexp!$C$4:$C$48 4=Summary!$C3),SVEexp!$H$4:$H$484)) Thank you, Jerry |
|
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
sumproduct
I changed the end range to
=IF($B3<=0,"",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. <Andy wrote in message ... 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" wrote in message ... 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=Summa ry!$B3)*(SVEexp!$C$4:$C$48 4=Summary!$C3),SVEexp!$H$4:$H$484)) Thank you, Jerry |
|
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
sumproduct
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. "Jerry Kinder" wrote in message ... I changed the end range to =IF($B3<=0,"",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. <Andy wrote in message ... 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" wrote in message ... 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=Summa ry!$B3)*(SVEexp!$C$4:$C$48 4=Summary!$C3),SVEexp!$H$4:$H$484)) Thank you, Jerry |
|
#5
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
sumproduct
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 "the summary sheet" B C D Office Phone t he formula for totals Office Misc. Offcie Supplies Wages Sue Wages Bill <Andy wrote in message ... 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. "Jerry Kinder" wrote in message ... I changed the end range to =IF($B3<=0,"",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. <Andy wrote in message ... 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" wrote in message ... 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=Summa ry!$B3)*(SVEexp!$C$4:$C$48 4=Summary!$C3),SVEexp!$H$4:$H$484)) Thank you, Jerry |
|
#6
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
sumproduct
Jerry,
Try =IF($B3<=0,"",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) "Jerry Kinder" wrote in message ... 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 "the summary sheet" B C D Office Phone t he formula for totals Office Misc. Offcie Supplies Wages Sue Wages Bill <Andy wrote in message ... 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. "Jerry Kinder" wrote in message ... I changed the end range to =IF($B3<=0,"",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. <Andy wrote in message ... 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" wrote in message ... 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=Summa ry!$B3)*(SVEexp!$C$4:$C$48 4=Summary!$C3),SVEexp!$H$4:$H$484)) Thank you, Jerry |
|
#7
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
sumproduct
YES!!!! it is PERFECT.
Thank you, Thank you, Thank you, Thank you "Bob Phillips" wrote in message ... Jerry, Try =IF($B3<=0,"",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) "Jerry Kinder" wrote in message ... 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 "the summary sheet" B C D Office Phone t he formula for totals Office Misc. Offcie Supplies Wages Sue Wages Bill <Andy wrote in message ... 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. "Jerry Kinder" wrote in message ... I changed the end range to =IF($B3<=0,"",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. <Andy wrote in message ... 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" wrote in message ... 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=Summa ry!$B3)*(SVEexp!$C$4:$C$48 4=Summary!$C3),SVEexp!$H$4:$H$484)) Thank you, Jerry |
|
#8
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
sumproduct
It's a pleasure (4 times) <vbg
-- HTH Bob Phillips (remove nothere from the email address if mailing direct) "Jerry Kinder" wrote in message ... YES!!!! it is PERFECT. Thank you, Thank you, Thank you, Thank you "Bob Phillips" wrote in message ... Jerry, Try =IF($B3<=0,"",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) "Jerry Kinder" wrote in message ... 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 "the summary sheet" B C D Office Phone t he formula for totals Office Misc. Offcie Supplies Wages Sue Wages Bill <Andy wrote in message ... 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. "Jerry Kinder" wrote in message ... I changed the end range to =IF($B3<=0,"",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. <Andy wrote in message ... 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" wrote in message ... 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=Summa ry!$B3)*(SVEexp!$C$4:$C$48 4=Summary!$C3),SVEexp!$H$4:$H$484)) Thank you, Jerry |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Sumproduct | Excel Worksheet Functions | |||
| sumproduct causing memory errors? | Excel Worksheet Functions | |||
| Can I reference =, <, or > sign in SUMPRODUCT | Excel Discussion (Misc queries) | |||
| Sumproduct function not working | Excel Worksheet Functions | |||
| adding two sumproduct formulas together | Excel Worksheet Functions | |||
Linear Mode
