Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
What's Wrong With This Formula?
What's Wrong With This Formula? =SUMPRODUCT((C11:$C$65536="I*")*(F11:$F$65536=F10) *(E11:$E$65536)) - I'm using Excel 2000 - Column C contains text values - Column F contains text values - Column E contains numeric values - My formula is in cell E10 - I want to sum cells in column E below the row containing this formula using the following criteria... - This formula is returning 0 Criteria: 1) cells in column C whose text values start with "I" 2) cells in column F whose text values equals the text value in cell F10 Note: - The purpose of the mixed references (i.e. relative/absolute) is because I need to copy this formula down but it should just evaluate cells below the row containing the formula. - I've tried using F11 instead of F10 but I'm still returning 0 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
What's Wrong With This Formula?
=SUMPRODUCT((LEFT(C11:$C$65536,1)="I")*(F11:$F$655 36=F10)*(E11:$E$65536))
(You can't use wildcards like that.) As an aside, if you need all those rows, fine. But your formula will recalculate much quicker if you limit the range to what you need (or a little more for safety???). Excel Nut wrote: What's Wrong With This Formula? =SUMPRODUCT((C11:$C$65536="I*")*(F11:$F$65536=F10) *(E11:$E$65536)) - I'm using Excel 2000 - Column C contains text values - Column F contains text values - Column E contains numeric values - My formula is in cell E10 - I want to sum cells in column E below the row containing this formula using the following criteria... - This formula is returning 0 Criteria: 1) cells in column C whose text values start with "I" 2) cells in column F whose text values equals the text value in cell F10 Note: - The purpose of the mixed references (i.e. relative/absolute) is because I need to copy this formula down but it should just evaluate cells below the row containing the formula. - I've tried using F11 instead of F10 but I'm still returning 0 -- Dave Peterson |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
What's Wrong With This Formula?
On Feb 2, 10:32*am, Dave Peterson wrote:
=SUMPRODUCT((LEFT(C11:$C$65536,1)="I")*(F11:$F$655 36=F10)*(E11:$E$65536)) (You can't use wildcards like that.) As an aside, if you need all those rows, fine. *But your formula will recalculate much quicker if you limit the range to what you need (or a little more for safety???). Excel Nut wrote: What's Wrong With This Formula? =SUMPRODUCT((C11:$C$65536="I*")*(F11:$F$65536=F10) *(E11:$E$65536)) - I'm using Excel 2000 - Column C contains text values - Column F contains text values - Column E contains numeric values - My formula is in cell E10 - I want to sum cells in column E below the row containing this formula using the following criteria... - This formula is returning 0 Criteria: 1) cells in column C whose text values start with "I" 2) cells in column F whose text values equals the text value in cell F10 Note: - The purpose of the mixed references (i.e. relative/absolute) is because I need to copy this formula down but it should just evaluate cells below the row containing the formula. - I've tried using F11 instead of F10 but I'm still returning 0 -- Dave Peterson- Hide quoted text - - Show quoted text - Darn! Wildcards worked in my SUMIF formulas but this one I have two criteria and that's why I used SUMPRODUCT. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
What's Wrong With This Formula?
Excel Nut;209408 Wrote: On Feb 2, 10:32*am, Dave Peterson wrote: =SUMPRODUCT((LEFT(C11:$C$65536,1)="I")*(F11:$F$655 36=F10)*(E11:$E$65536)) (You can't use wildcards like that.) As an aside, if you need all those rows, fine. *But your formula will recalculate much quicker if you limit the range to what you need (or a little more for safety???). Excel Nut wrote: What's Wrong With This Formula? =SUMPRODUCT((C11:$C$65536="I*")*(F11:$F$65536=F10) *(E11:$E$65536)) - I'm using Excel 2000 - Column C contains text values - Column F contains text values - Column E contains numeric values - My formula is in cell E10 - I want to sum cells in column E below the row containing this formula using the following criteria... - This formula is returning 0 Criteria: 1) cells in column C whose text values start with "I" 2) cells in column F whose text values equals the text value in cell F10 Note: - The purpose of the mixed references (i.e. relative/absolute) is because I need to copy this formula down but it should just evaluate cells below the row containing the formula. - I've tried using F11 instead of F10 but I'm still returning 0 -- Dave Peterson- Hide quoted text - - Show quoted text - Darn! Wildcards worked in my SUMIF formulas but this one I have two criteria and that's why I used SUMPRODUCT. Why do you need the wildcard ? -- Pecoflyer Cheers - *'Membership is free' (http://www.thecodecage.com)* & allows file upload -faster and better answers *Adding your XL version* to your post helps finding solution faster ------------------------------------------------------------------------ Pecoflyer's Profile: http://www.thecodecage.com/forumz/member.php?userid=14 View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=57460 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
What's Wrong With This Formula?
On Feb 2, 11:52*am, Pecoflyer
wrote: Excel Nut;209408 Wrote: On Feb 2, 10:32*am, Dave Peterson wrote: =SUMPRODUCT((LEFT(C11:$C$65536,1)="I")*(F11:$F$655 36=F10)*(E11:$E$65536)) (You can't use wildcards like that.) As an aside, if you need all those rows, fine. *But your formula will recalculate much quicker if you limit the range to what you need (or a little more for safety???). Excel Nut wrote: What's Wrong With This Formula? =SUMPRODUCT((C11:$C$65536="I*")*(F11:$F$65536=F10) *(E11:$E$65536)) - I'm using Excel 2000 - Column C contains text values - Column F contains text values - Column E contains numeric values - My formula is in cell E10 - I want to sum cells in column E below the row containing this formula using the following criteria... - This formula is returning 0 Criteria: 1) cells in column C whose text values start with "I" 2) cells in column F whose text values equals the text value in cell F10 Note: - The purpose of the mixed references (i.e. relative/absolute) is because I need to copy this formula down but it should just evaluate cells below the row containing the formula. - I've tried using F11 instead of F10 but I'm still returning 0 -- Dave Peterson- Hide quoted text - - Show quoted text - Darn! Wildcards worked in my SUMIF formulas but this one I have two criteria and that's why I used SUMPRODUCT. Why do you need the wildcard ? -- Pecoflyer Cheers - *'Membership is free' (http://www.thecodecage.com)*& allows file upload -faster and better answers *Adding your XL version* to your post helps finding solution faster ------------------------------------------------------------------------ Pecoflyer's Profile:http://www.thecodecage.com/forumz/member.php?userid=14 View this thread:http://www.thecodecage.com/forumz/sh...d.php?t=57460- Hide quoted text - - Show quoted text - Here's a sample of my data (if you want to copy and parse it)... Row|Column C|Column D|Column E|Column F Row 1|J0000000042900|JAN|8.64|ALLOC FAX Row 2|I0000000299000|JAN|18.94|ALLOC FAX Row 3|I3001195880030|JAN|1.25|ALLOC FAX Row 4|I3001310880030|JAN|17.69|ALLOC FAX Row 5|J0101170880030|JAN|2.86|ALLOC FAX Row 6|J0101175880030|JAN|0.11|ALLOC FAX Row 7|J0101180880030|JAN|5.67|ALLOC FAX Row 8|J0000000042900|JAN|30.29|ALLOC PHONE Row 9|I0000000299000|JAN|28.35|ALLOC PHONE Row 10|I3001195880040|JAN|0.25|ALLOC PHONE Row 11|I3001195880040|JAN|1.39|ALLOC PHONE Row 12|I3001196880040|JAN|2.97|ALLOC PHONE Row 13|I3001210880040|JAN|4.99|ALLOC PHONE Row 14|I3001310880040|JAN|18.75|ALLOC PHONE Row 15|J0101170880040|JAN|23.65|ALLOC PHONE Row 16|J0101175880040|JAN|2.84|ALLOC PHONE Row 17|J0101180880040|JAN|1.85|ALLOC PHONE Row 18|J0101185880040|JAN|1.95|ALLOC PHONE I want a formula for cell E1 that will evaluate all rows below the formula row that have values in Column C starting wiht "I" and have values in column F that match the value in F1. I want to be able to copy the same formula to E9 and 58 other cells further down, This is the formula I tried but Dave said I cannot use wildcards. =SUMPRODUCT((C2:$C$3000="I*")*(F2:$F$3000=F1)*(E2: $E$3000)) I know I can use a SUMIF formula to sum all column C values starting with "I". =SUMIF(C1:C7,"I*",E1:E7) But I also need to match values in column F with the value in column F on the same row as the formula. If I could get this to work, I could copy the same formula down for all sixty I0000000299000 totals without needing to change the cell references, otherwise I will need to use 60 SUMIF formulas with different references for each one. Thanks for looking at this. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
What's Wrong With This Formula?
On Feb 2, 1:11*pm, Excel Nut wrote:
On Feb 2, 11:52*am, Pecoflyer wrote: Excel Nut;209408 Wrote: On Feb 2, 10:32*am, Dave Peterson wrote: =SUMPRODUCT((LEFT(C11:$C$65536,1)="I")*(F11:$F$655 36=F10)*(E11:$E$65536)) (You can't use wildcards like that.) As an aside, if you need all those rows, fine. *But your formula will recalculate much quicker if you limit the range to what you need (or a little more for safety???). Excel Nut wrote: What's Wrong With This Formula? =SUMPRODUCT((C11:$C$65536="I*")*(F11:$F$65536=F10) *(E11:$E$65536)) - I'm using Excel 2000 - Column C contains text values - Column F contains text values - Column E contains numeric values - My formula is in cell E10 - I want to sum cells in column E below the row containing this formula using the following criteria... - This formula is returning 0 Criteria: 1) cells in column C whose text values start with "I" 2) cells in column F whose text values equals the text value in cell F10 Note: - The purpose of the mixed references (i.e. relative/absolute) is because I need to copy this formula down but it should just evaluate cells below the row containing the formula. - I've tried using F11 instead of F10 but I'm still returning 0 -- Dave Peterson- Hide quoted text - - Show quoted text - Darn! Wildcards worked in my SUMIF formulas but this one I have two criteria and that's why I used SUMPRODUCT. Why do you need the wildcard ? -- Pecoflyer Cheers - *'Membership is free' (http://www.thecodecage.com)*&allows file upload -faster and better answers *Adding your XL version* to your post helps finding solution faster ------------------------------------------------------------------------ Pecoflyer's Profile:http://www.thecodecage.com/forumz/member.php?userid=14 View this thread:http://www.thecodecage.com/forumz/sh...p?t=57460-Hide quoted text - - Show quoted text - Here's a sample of my data (if you want to copy and parse it)... Row|Column C|Column D|Column E|Column F Row 1|J0000000042900|JAN|8.64|ALLOC FAX Row 2|I0000000299000|JAN|18.94|ALLOC FAX Row 3|I3001195880030|JAN|1.25|ALLOC FAX Row 4|I3001310880030|JAN|17.69|ALLOC FAX Row 5|J0101170880030|JAN|2.86|ALLOC FAX Row 6|J0101175880030|JAN|0.11|ALLOC FAX Row 7|J0101180880030|JAN|5.67|ALLOC FAX Row 8|J0000000042900|JAN|30.29|ALLOC PHONE Row 9|I0000000299000|JAN|28.35|ALLOC PHONE Row 10|I3001195880040|JAN|0.25|ALLOC PHONE Row 11|I3001195880040|JAN|1.39|ALLOC PHONE Row 12|I3001196880040|JAN|2.97|ALLOC PHONE Row 13|I3001210880040|JAN|4.99|ALLOC PHONE Row 14|I3001310880040|JAN|18.75|ALLOC PHONE Row 15|J0101170880040|JAN|23.65|ALLOC PHONE Row 16|J0101175880040|JAN|2.84|ALLOC PHONE Row 17|J0101180880040|JAN|1.85|ALLOC PHONE Row 18|J0101185880040|JAN|1.95|ALLOC PHONE I want a formula for cell E1 that will evaluate all rows below the formula row that have values in Column C starting wiht "I" and have values in column F that match the value in F1. I want to be able to copy the same formula to E9 and 58 other cells further down, This is the formula I tried but Dave said I cannot use wildcards. =SUMPRODUCT((C2:$C$3000="I*")*(F2:$F$3000=F1)*(E2: $E$3000)) I know I can use a SUMIF formula to sum all column C values starting with "I". =SUMIF(C1:C7,"I*",E1:E7) But I also need to match values in column F with the value in column F on the same row as the formula. If I could get this to work, I could copy the same formula down for all sixty I0000000299000 totals without needing to change the cell references, otherwise I will need to use 60 SUMIF formulas with different references for each one. Thanks for looking at this.- Hide quoted text - - Show quoted text - WAIT ! ! messed up the references in that last post... Let me repost. |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
What's Wrong With This Formula?
Did you actually try the formula that Dave gave you? i.e.:
=SUMPRODUCT((LEFT(C11:$C$65536,1)="I")*(F11:$F$655 36=F10)*(E11:$E $65536)) His comment meant that you don't use wildcards like that in SP - instead you can use the LEFT function. Pete On Feb 2, 4:11*pm, Excel Nut wrote: Here's a sample of my data (if you want to copy and parse it)... Row|Column C|Column D|Column E|Column F Row 1|J0000000042900|JAN|8.64|ALLOC FAX Row 2|I0000000299000|JAN|18.94|ALLOC FAX Row 3|I3001195880030|JAN|1.25|ALLOC FAX Row 4|I3001310880030|JAN|17.69|ALLOC FAX Row 5|J0101170880030|JAN|2.86|ALLOC FAX Row 6|J0101175880030|JAN|0.11|ALLOC FAX Row 7|J0101180880030|JAN|5.67|ALLOC FAX Row 8|J0000000042900|JAN|30.29|ALLOC PHONE Row 9|I0000000299000|JAN|28.35|ALLOC PHONE Row 10|I3001195880040|JAN|0.25|ALLOC PHONE Row 11|I3001195880040|JAN|1.39|ALLOC PHONE Row 12|I3001196880040|JAN|2.97|ALLOC PHONE Row 13|I3001210880040|JAN|4.99|ALLOC PHONE Row 14|I3001310880040|JAN|18.75|ALLOC PHONE Row 15|J0101170880040|JAN|23.65|ALLOC PHONE Row 16|J0101175880040|JAN|2.84|ALLOC PHONE Row 17|J0101180880040|JAN|1.85|ALLOC PHONE Row 18|J0101185880040|JAN|1.95|ALLOC PHONE I want a formula for cell E1 that will evaluate all rows below the formula row that have values in Column C starting wiht "I" and have values in column F that match the value in F1. I want to be able to copy the same formula to E9 and 58 other cells further down, This is the formula I tried but Dave said I cannot use wildcards. =SUMPRODUCT((C2:$C$3000="I*")*(F2:$F$3000=F1)*(E2: $E$3000)) I know I can use a SUMIF formula to sum all column C values starting with "I". =SUMIF(C1:C7,"I*",E1:E7) But I also need to match values in column F with the value in column F on the same row as the formula. If I could get this to work, I could copy the same formula down for all sixty I0000000299000 totals without needing to change the cell references, otherwise I will need to use 60 SUMIF formulas with different references for each one. Thanks for looking at this |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
What's Wrong With This Formula?
On Feb 2, 1:11*pm, Excel Nut wrote:
On Feb 2, 11:52*am, Pecoflyer wrote: Excel Nut;209408 Wrote: On Feb 2, 10:32*am, Dave Peterson wrote: =SUMPRODUCT((LEFT(C11:$C$65536,1)="I")*(F11:$F$655 36=F10)*(E11:$E$65536)) (You can't use wildcards like that.) As an aside, if you need all those rows, fine. *But your formula will recalculate much quicker if you limit the range to what you need (or a little more for safety???). Excel Nut wrote: What's Wrong With This Formula? =SUMPRODUCT((C11:$C$65536="I*")*(F11:$F$65536=F10) *(E11:$E$65536)) - I'm using Excel 2000 - Column C contains text values - Column F contains text values - Column E contains numeric values - My formula is in cell E10 - I want to sum cells in column E below the row containing this formula using the following criteria... - This formula is returning 0 Criteria: 1) cells in column C whose text values start with "I" 2) cells in column F whose text values equals the text value in cell F10 Note: - The purpose of the mixed references (i.e. relative/absolute) is because I need to copy this formula down but it should just evaluate cells below the row containing the formula. - I've tried using F11 instead of F10 but I'm still returning 0 -- Dave Peterson- Hide quoted text - - Show quoted text - Darn! Wildcards worked in my SUMIF formulas but this one I have two criteria and that's why I used SUMPRODUCT. Why do you need the wildcard ? -- Pecoflyer Cheers - *'Membership is free' (http://www.thecodecage.com)*&allows file upload -faster and better answers *Adding your XL version* to your post helps finding solution faster ------------------------------------------------------------------------ Pecoflyer's Profile:http://www.thecodecage.com/forumz/member.php?userid=14 View this thread:http://www.thecodecage.com/forumz/sh...p?t=57460-Hide quoted text - - Show quoted text - Here's a sample of my data (if you want to copy and parse it)... Row|Column C|Column D|Column E|Column F Row 1|J0000000042900|JAN|8.64|ALLOC FAX Row 2|I0000000299000|JAN|18.94|ALLOC FAX Row 3|I3001195880030|JAN|1.25|ALLOC FAX Row 4|I3001310880030|JAN|17.69|ALLOC FAX Row 5|J0101170880030|JAN|2.86|ALLOC FAX Row 6|J0101175880030|JAN|0.11|ALLOC FAX Row 7|J0101180880030|JAN|5.67|ALLOC FAX Row 8|J0000000042900|JAN|30.29|ALLOC PHONE Row 9|I0000000299000|JAN|28.35|ALLOC PHONE Row 10|I3001195880040|JAN|0.25|ALLOC PHONE Row 11|I3001195880040|JAN|1.39|ALLOC PHONE Row 12|I3001196880040|JAN|2.97|ALLOC PHONE Row 13|I3001210880040|JAN|4.99|ALLOC PHONE Row 14|I3001310880040|JAN|18.75|ALLOC PHONE Row 15|J0101170880040|JAN|23.65|ALLOC PHONE Row 16|J0101175880040|JAN|2.84|ALLOC PHONE Row 17|J0101180880040|JAN|1.85|ALLOC PHONE Row 18|J0101185880040|JAN|1.95|ALLOC PHONE I want a formula for cell E1 that will evaluate all rows below the formula row that have values in Column C starting wiht "I" and have values in column F that match the value in F1. I want to be able to copy the same formula to E9 and 58 other cells further down, This is the formula I tried but Dave said I cannot use wildcards. =SUMPRODUCT((C2:$C$3000="I*")*(F2:$F$3000=F1)*(E2: $E$3000)) I know I can use a SUMIF formula to sum all column C values starting with "I". =SUMIF(C1:C7,"I*",E1:E7) But I also need to match values in column F with the value in column F on the same row as the formula. If I could get this to work, I could copy the same formula down for all sixty I0000000299000 totals without needing to change the cell references, otherwise I will need to use 60 SUMIF formulas with different references for each one. Thanks for looking at this.- Hide quoted text - - Show quoted text - Here's a sample of my data (if you want to copy and parse it)... Row|Column C|Column D|Column E|Column F Row 1|J0000000042900|JAN|8.64|ALLOC FAX Row 2|I0000000299000|JAN|18.94|ALLOC FAX Row 3|I3001195880030|JAN|1.25|ALLOC FAX Row 4|I3001310880030|JAN|17.69|ALLOC FAX Row 5|J0101170880030|JAN|2.86|ALLOC FAX Row 6|J0101175880030|JAN|0.11|ALLOC FAX Row 7|J0101180880030|JAN|5.67|ALLOC FAX Row 8|J0000000042900|JAN|30.29|ALLOC PHONE Row 9|I0000000299000|JAN|28.35|ALLOC PHONE Row 10|I3001195880040|JAN|0.25|ALLOC PHONE Row 11|I3001195880040|JAN|1.39|ALLOC PHONE Row 12|I3001196880040|JAN|2.97|ALLOC PHONE Row 13|I3001210880040|JAN|4.99|ALLOC PHONE Row 14|I3001310880040|JAN|18.75|ALLOC PHONE Row 15|J0101170880040|JAN|23.65|ALLOC PHONE Row 16|J0101175880040|JAN|2.84|ALLOC PHONE Row 17|J0101180880040|JAN|1.85|ALLOC PHONE Row 18|J0101185880040|JAN|1.95|ALLOC PHONE I want a formula for cell E2 that will evaluate all rows below the formula row that have values in Column C starting with "I" and have values in column F that match the value in F2. I want to be able to copy the same formula to E9 and 58 other cells further down, This is the formula I tried but Dave said I cannot use wildcards. =SUMPRODUCT((C3:$C$3000="I*")*(F3:$F$3000=F2)*(E3: $E$3000)) I know I can use a SUMIF formula to sum all column C values starting with "I". =SUMIF(C3:C7,"I*",E3:E7) But I also need to match values in column F with the value in column F on the same row as the formula. If I could get this to work, I could copy the same formula down for all sixty I0000000299000 totals without needing to change the cell references, otherwise I will need to use 60 SUMIF formulas with different references for each one. Thanks for looking at this. Excel Nut |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
What's Wrong With This Formula?
On Feb 2, 1:21*pm, Pete_UK wrote:
Did you actually try the formula that Dave gave you? i.e.: =SUMPRODUCT((LEFT(C11:$C$65536,1)="I")*(F11:$F$655 36=F10)*(E11:$E $65536)) His comment meant that you don't use wildcards like that in SP - instead you can use the LEFT function. Pete On Feb 2, 4:11*pm, Excel Nut wrote: Here's a sample of my data (if you want to copy and parse it)... Row|Column C|Column D|Column E|Column F Row 1|J0000000042900|JAN|8.64|ALLOC FAX Row 2|I0000000299000|JAN|18.94|ALLOC FAX Row 3|I3001195880030|JAN|1.25|ALLOC FAX Row 4|I3001310880030|JAN|17.69|ALLOC FAX Row 5|J0101170880030|JAN|2.86|ALLOC FAX Row 6|J0101175880030|JAN|0.11|ALLOC FAX Row 7|J0101180880030|JAN|5.67|ALLOC FAX Row 8|J0000000042900|JAN|30.29|ALLOC PHONE Row 9|I0000000299000|JAN|28.35|ALLOC PHONE Row 10|I3001195880040|JAN|0.25|ALLOC PHONE Row 11|I3001195880040|JAN|1.39|ALLOC PHONE Row 12|I3001196880040|JAN|2.97|ALLOC PHONE Row 13|I3001210880040|JAN|4.99|ALLOC PHONE Row 14|I3001310880040|JAN|18.75|ALLOC PHONE Row 15|J0101170880040|JAN|23.65|ALLOC PHONE Row 16|J0101175880040|JAN|2.84|ALLOC PHONE Row 17|J0101180880040|JAN|1.85|ALLOC PHONE Row 18|J0101185880040|JAN|1.95|ALLOC PHONE I want a formula for cell E1 that will evaluate all rows below the formula row that have values in Column C starting wiht "I" and have values in column F that match the value in F1. I want to be able to copy the same formula to E9 and 58 other cells further down, This is the formula I tried but Dave said I cannot use wildcards. =SUMPRODUCT((C2:$C$3000="I*")*(F2:$F$3000=F1)*(E2: $E$3000)) I know I can use a SUMIF formula to sum all column C values starting with "I". =SUMIF(C1:C7,"I*",E1:E7) But I also need to match values in column F with the value in column F on the same row as the formula. If I could get this to work, I could copy the same formula down for all sixty I0000000299000 totals without needing to change the cell references, otherwise I will need to use 60 SUMIF formulas with different references for each one. Thanks for looking at this- Hide quoted text - - Show quoted text - Sorry. I didn't notice that he had suggested a new formula. I thought he had just put my original formula above hhis explanation of why it wasn't working. Yes, I have since tried Dave's formula and it works fine, except that I had to modify it to refer just to the cells containing data. I had originally allowed for an extra 1000 or so rows below my data to accommodate new data to be added in the future. However, when I include a reference to these blank cells in my SUMPRODUCT formula, I get a #VALUE! error. Any way around that? Excel Nut Thanks Dave and Pete. |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
What's Wrong With This Formula?
Hi
try the formula provided by Dave, Adjust the range to suit yours and the row numbers to your dataset as the recalculation would take some time if you referenced all the rows in your spreadsheet Post back if its don't work citing the error you receive and the formula you use. HTH Pls provide your feedback -- Pls click on the Yes button below if this posting is helpful. Thank You cheers, francis "Excel Nut" wrote: On Feb 2, 1:11 pm, Excel Nut wrote: On Feb 2, 11:52 am, Pecoflyer wrote: Excel Nut;209408 Wrote: On Feb 2, 10:32*am, Dave Peterson wrote: =SUMPRODUCT((LEFT(C11:$C$65536,1)="I")*(F11:$F$655 36=F10)*(E11:$E$65536)) (You can't use wildcards like that.) As an aside, if you need all those rows, fine. *But your formula will recalculate much quicker if you limit the range to what you need (or a little more for safety???). Excel Nut wrote: What's Wrong With This Formula? =SUMPRODUCT((C11:$C$65536="I*")*(F11:$F$65536=F10) *(E11:$E$65536)) - I'm using Excel 2000 - Column C contains text values - Column F contains text values - Column E contains numeric values - My formula is in cell E10 - I want to sum cells in column E below the row containing this formula using the following criteria... - This formula is returning 0 Criteria: 1) cells in column C whose text values start with "I" 2) cells in column F whose text values equals the text value in cell F10 Note: - The purpose of the mixed references (i.e. relative/absolute) is because I need to copy this formula down but it should just evaluate cells below the row containing the formula. - I've tried using F11 instead of F10 but I'm still returning 0 -- Dave Peterson- Hide quoted text - - Show quoted text - Darn! Wildcards worked in my SUMIF formulas but this one I have two criteria and that's why I used SUMPRODUCT. Why do you need the wildcard ? -- Pecoflyer Cheers - *'Membership is free' (http://www.thecodecage.com)*&allows file upload -faster and better answers *Adding your XL version* to your post helps finding solution faster ------------------------------------------------------------------------ Pecoflyer's Profile:http://www.thecodecage.com/forumz/member.php?userid=14 View this thread:http://www.thecodecage.com/forumz/sh...p?t=57460-Hide quoted text - - Show quoted text - Here's a sample of my data (if you want to copy and parse it)... Row|Column C|Column D|Column E|Column F Row 1|J0000000042900|JAN|8.64|ALLOC FAX Row 2|I0000000299000|JAN|18.94|ALLOC FAX Row 3|I3001195880030|JAN|1.25|ALLOC FAX Row 4|I3001310880030|JAN|17.69|ALLOC FAX Row 5|J0101170880030|JAN|2.86|ALLOC FAX Row 6|J0101175880030|JAN|0.11|ALLOC FAX Row 7|J0101180880030|JAN|5.67|ALLOC FAX Row 8|J0000000042900|JAN|30.29|ALLOC PHONE Row 9|I0000000299000|JAN|28.35|ALLOC PHONE Row 10|I3001195880040|JAN|0.25|ALLOC PHONE Row 11|I3001195880040|JAN|1.39|ALLOC PHONE Row 12|I3001196880040|JAN|2.97|ALLOC PHONE Row 13|I3001210880040|JAN|4.99|ALLOC PHONE Row 14|I3001310880040|JAN|18.75|ALLOC PHONE Row 15|J0101170880040|JAN|23.65|ALLOC PHONE Row 16|J0101175880040|JAN|2.84|ALLOC PHONE Row 17|J0101180880040|JAN|1.85|ALLOC PHONE Row 18|J0101185880040|JAN|1.95|ALLOC PHONE I want a formula for cell E1 that will evaluate all rows below the formula row that have values in Column C starting wiht "I" and have values in column F that match the value in F1. I want to be able to copy the same formula to E9 and 58 other cells further down, This is the formula I tried but Dave said I cannot use wildcards. =SUMPRODUCT((C2:$C$3000="I*")*(F2:$F$3000=F1)*(E2: $E$3000)) I know I can use a SUMIF formula to sum all column C values starting with "I". =SUMIF(C1:C7,"I*",E1:E7) But I also need to match values in column F with the value in column F on the same row as the formula. If I could get this to work, I could copy the same formula down for all sixty I0000000299000 totals without needing to change the cell references, otherwise I will need to use 60 SUMIF formulas with different references for each one. Thanks for looking at this.- Hide quoted text - - Show quoted text - Here's a sample of my data (if you want to copy and parse it)... Row|Column C|Column D|Column E|Column F Row 1|J0000000042900|JAN|8.64|ALLOC FAX Row 2|I0000000299000|JAN|18.94|ALLOC FAX Row 3|I3001195880030|JAN|1.25|ALLOC FAX Row 4|I3001310880030|JAN|17.69|ALLOC FAX Row 5|J0101170880030|JAN|2.86|ALLOC FAX Row 6|J0101175880030|JAN|0.11|ALLOC FAX Row 7|J0101180880030|JAN|5.67|ALLOC FAX Row 8|J0000000042900|JAN|30.29|ALLOC PHONE Row 9|I0000000299000|JAN|28.35|ALLOC PHONE Row 10|I3001195880040|JAN|0.25|ALLOC PHONE Row 11|I3001195880040|JAN|1.39|ALLOC PHONE Row 12|I3001196880040|JAN|2.97|ALLOC PHONE Row 13|I3001210880040|JAN|4.99|ALLOC PHONE Row 14|I3001310880040|JAN|18.75|ALLOC PHONE Row 15|J0101170880040|JAN|23.65|ALLOC PHONE Row 16|J0101175880040|JAN|2.84|ALLOC PHONE Row 17|J0101180880040|JAN|1.85|ALLOC PHONE Row 18|J0101185880040|JAN|1.95|ALLOC PHONE I want a formula for cell E2 that will evaluate all rows below the formula row that have values in Column C starting with "I" and have values in column F that match the value in F2. I want to be able to copy the same formula to E9 and 58 other cells further down, This is the formula I tried but Dave said I cannot use wildcards. =SUMPRODUCT((C3:$C$3000="I*")*(F3:$F$3000=F2)*(E3: $E$3000)) I know I can use a SUMIF formula to sum all column C values starting with "I". =SUMIF(C3:C7,"I*",E3:E7) But I also need to match values in column F with the value in column F on the same row as the formula. If I could get this to work, I could copy the same formula down for all sixty I0000000299000 totals without needing to change the cell references, otherwise I will need to use 60 SUMIF formulas with different references for each one. Thanks for looking at this. Excel Nut |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
What's Wrong With This Formula?
On Feb 2, 2:43*pm, xlmate wrote:
Hi try the formula provided by Dave, Adjust the range to suit yours and the row numbers to your dataset as the recalculation would take some time if you referenced all the rows in your spreadsheet Post back if its don't work citing the error you receive and the formula you use. HTH Pls provide your feedback -- Pls click on the Yes button below if this posting is helpful. Thank You cheers, francis "Excel Nut" wrote: On Feb 2, 1:11 pm, Excel Nut wrote: On Feb 2, 11:52 am, Pecoflyer wrote: Excel Nut;209408 Wrote: On Feb 2, 10:32*am, Dave Peterson wrote: =SUMPRODUCT((LEFT(C11:$C$65536,1)="I")*(F11:$F$655 36=F10)*(E11:$E$65536)) (You can't use wildcards like that.) As an aside, if you need all those rows, fine. *But your formula will recalculate much quicker if you limit the range to what you need (or a little more for safety???). Excel Nut wrote: What's Wrong With This Formula? =SUMPRODUCT((C11:$C$65536="I*")*(F11:$F$65536=F10) *(E11:$E$65536)) - I'm using Excel 2000 - Column C contains text values - Column F contains text values - Column E contains numeric values - My formula is in cell E10 - I want to sum cells in column E below the row containing this formula using the following criteria... - This formula is returning 0 Criteria: 1) cells in column C whose text values start with "I" 2) cells in column F whose text values equals the text value in cell F10 Note: - The purpose of the mixed references (i.e. relative/absolute) is because I need to copy this formula down but it should just evaluate cells below the row containing the formula. - I've tried using F11 instead of F10 but I'm still returning 0 -- Dave Peterson- Hide quoted text - - Show quoted text - Darn! Wildcards worked in my SUMIF formulas but this one I have two criteria and that's why I used SUMPRODUCT. Why do you need the wildcard ? -- Pecoflyer Cheers - *'Membership is free' (http://www.thecodecage.com)*&allowsfile upload -faster and better answers *Adding your XL version* to your post helps finding solution faster ------------------------------------------------------------------------ Pecoflyer's Profile:http://www.thecodecage.com/forumz/member.php?userid=14 View this thread:http://www.thecodecage.com/forumz/sh...460-Hidequoted text - - Show quoted text - Here's a sample of my data (if you want to copy and parse it)... Row|Column C|Column D|Column E|Column F Row 1|J0000000042900|JAN|8.64|ALLOC FAX Row 2|I0000000299000|JAN|18.94|ALLOC FAX Row 3|I3001195880030|JAN|1.25|ALLOC FAX Row 4|I3001310880030|JAN|17.69|ALLOC FAX Row 5|J0101170880030|JAN|2.86|ALLOC FAX Row 6|J0101175880030|JAN|0.11|ALLOC FAX Row 7|J0101180880030|JAN|5.67|ALLOC FAX Row 8|J0000000042900|JAN|30.29|ALLOC PHONE Row 9|I0000000299000|JAN|28.35|ALLOC PHONE Row 10|I3001195880040|JAN|0.25|ALLOC PHONE Row 11|I3001195880040|JAN|1.39|ALLOC PHONE Row 12|I3001196880040|JAN|2.97|ALLOC PHONE Row 13|I3001210880040|JAN|4.99|ALLOC PHONE Row 14|I3001310880040|JAN|18.75|ALLOC PHONE Row 15|J0101170880040|JAN|23.65|ALLOC PHONE Row 16|J0101175880040|JAN|2.84|ALLOC PHONE Row 17|J0101180880040|JAN|1.85|ALLOC PHONE Row 18|J0101185880040|JAN|1.95|ALLOC PHONE I want a formula for cell E1 that will evaluate all rows below the formula row that have values in Column C starting wiht "I" and have values in column F that match the value in F1. I want to be able to copy the same formula to E9 and 58 other cells further down, This is the formula I tried but Dave said I cannot use wildcards. =SUMPRODUCT((C2:$C$3000="I*")*(F2:$F$3000=F1)*(E2: $E$3000)) I know I can use a SUMIF formula to sum all column C values starting with "I". =SUMIF(C1:C7,"I*",E1:E7) But I also need to match values in column F with the value in column F on the same row as the formula. If I could get this to work, I could copy the same formula down for all sixty I0000000299000 totals without needing to change the cell references, otherwise I will need to use 60 SUMIF formulas with different references for each one. Thanks for looking at this.- Hide quoted text - - Show quoted text - Here's a sample of my data (if you want to copy and parse it)... Row|Column C|Column D|Column E|Column F Row 1|J0000000042900|JAN|8.64|ALLOC FAX Row 2|I0000000299000|JAN|18.94|ALLOC FAX Row 3|I3001195880030|JAN|1.25|ALLOC FAX Row 4|I3001310880030|JAN|17.69|ALLOC FAX Row 5|J0101170880030|JAN|2.86|ALLOC FAX Row 6|J0101175880030|JAN|0.11|ALLOC FAX Row 7|J0101180880030|JAN|5.67|ALLOC FAX Row 8|J0000000042900|JAN|30.29|ALLOC PHONE Row 9|I0000000299000|JAN|28.35|ALLOC PHONE Row 10|I3001195880040|JAN|0.25|ALLOC PHONE Row 11|I3001195880040|JAN|1.39|ALLOC PHONE Row 12|I3001196880040|JAN|2.97|ALLOC PHONE Row 13|I3001210880040|JAN|4.99|ALLOC PHONE Row 14|I3001310880040|JAN|18.75|ALLOC PHONE Row 15|J0101170880040|JAN|23.65|ALLOC PHONE Row 16|J0101175880040|JAN|2.84|ALLOC PHONE Row 17|J0101180880040|JAN|1.85|ALLOC PHONE Row 18|J0101185880040|JAN|1.95|ALLOC PHONE I want a formula for cell E2 that will evaluate all rows below the formula row that have values in Column C starting with "I" and have values in column F that match the value in F2. I want to be able to copy the same formula to E9 and 58 other cells further down, This is the formula I tried but Dave said I cannot use wildcards. =SUMPRODUCT((C3:$C$3000="I*")*(F3:$F$3000=F2)*(E3: $E$3000)) I know I can use a SUMIF formula to sum all column C values starting with "I". =SUMIF(C3:C7,"I*",E3:E7) But I also need to match values in column F with the value in column F on the same row as the formula. If I could get this to work, I could copy the same formula down for all sixty I0000000299000 totals without needing to change the cell references, otherwise I will need to use 60 SUMIF formulas with different references for each one. Thanks for looking at this. Excel Nut- Hide quoted text - - Show quoted text - =SUMPRODUCT((C3:$C$3000="I*")*(F3:$F$3000=F2)*(E3: $E$3000)) I have adjusted the formula so that it doesn't refer to ALL rows. Currently my data goes down only to row 1541 but I need to reference some extra rows below my current data to allow for additional data to be added in the future. But when the SUMPRODUCT formula refers to these empty rows it returns a #VALUE! error. Any way around that? |
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
What's Wrong With This Formula?
I've adjusted the range to 2000, and included a further check:
=SUMPRODUCT((C11:C2000<"")*(LEFT(C11:C2000,1)="I" )*(F11:F2000=F10)* (E11:E2000)) Just check that you have true blanks in column E. Hope this helps. Pete On Feb 2, 5:35*pm, Excel Nut wrote: On Feb 2, 1:21*pm, Pete_UK wrote: Did you actually try the formula that Dave gave you? i.e.: =SUMPRODUCT((LEFT(C11:$C$65536,1)="I")*(F11:$F$655 36=F10)*(E11:$E $65536)) His comment meant that you don't use wildcards like that in SP - instead you can use the LEFT function. Pete On Feb 2, 4:11*pm, Excel Nut wrote: Here's a sample of my data (if you want to copy and parse it)... Row|Column C|Column D|Column E|Column F Row 1|J0000000042900|JAN|8.64|ALLOC FAX Row 2|I0000000299000|JAN|18.94|ALLOC FAX Row 3|I3001195880030|JAN|1.25|ALLOC FAX Row 4|I3001310880030|JAN|17.69|ALLOC FAX Row 5|J0101170880030|JAN|2.86|ALLOC FAX Row 6|J0101175880030|JAN|0.11|ALLOC FAX Row 7|J0101180880030|JAN|5.67|ALLOC FAX Row 8|J0000000042900|JAN|30.29|ALLOC PHONE Row 9|I0000000299000|JAN|28.35|ALLOC PHONE Row 10|I3001195880040|JAN|0.25|ALLOC PHONE Row 11|I3001195880040|JAN|1.39|ALLOC PHONE Row 12|I3001196880040|JAN|2.97|ALLOC PHONE Row 13|I3001210880040|JAN|4.99|ALLOC PHONE Row 14|I3001310880040|JAN|18.75|ALLOC PHONE Row 15|J0101170880040|JAN|23.65|ALLOC PHONE Row 16|J0101175880040|JAN|2.84|ALLOC PHONE Row 17|J0101180880040|JAN|1.85|ALLOC PHONE Row 18|J0101185880040|JAN|1.95|ALLOC PHONE I want a formula for cell E1 that will evaluate all rows below the formula row that have values in Column C starting wiht "I" and have values in column F that match the value in F1. I want to be able to copy the same formula to E9 and 58 other cells further down, This is the formula I tried but Dave said I cannot use wildcards. =SUMPRODUCT((C2:$C$3000="I*")*(F2:$F$3000=F1)*(E2: $E$3000)) I know I can use a SUMIF formula to sum all column C values starting with "I". =SUMIF(C1:C7,"I*",E1:E7) But I also need to match values in column F with the value in column F on the same row as the formula. If I could get this to work, I could copy the same formula down for all sixty I0000000299000 totals without needing to change the cell references, otherwise I will need to use 60 SUMIF formulas with different references for each one. Thanks for looking at this- Hide quoted text - - Show quoted text - Sorry. I didn't notice that he had suggested a new formula. I thought he had just put my original formula above hhis explanation of why it wasn't working. Yes, I have since tried Dave's formula and it works fine, except that I had to modify it to refer just to the cells containing data. I had originally allowed for an extra 1000 or so rows below my data to accommodate new data to be added in the future. However, when I include a reference to these blank cells in my SUMPRODUCT formula, I get a #VALUE! error. Any way around that? Excel Nut Thanks Dave and Pete.- Hide quoted text - - Show quoted text - |
#13
Posted to microsoft.public.excel.misc
|
|||
|
|||
What's Wrong With This Formula?
Pete_UK;210010 Wrote: I've adjusted the range to 2000, and included a further check: =SUMPRODUCT((C11:C2000<"")*(LEFT(C11:C2000,1)="I" )*(F11:F2000=F10)* (E11:E2000)) Just check that you have true blanks in column E. Hope this helps. Pete On Feb 2, 5:35*pm, Excel Nut wrote: On Feb 2, 1:21*pm, Pete_UK wrote: Did you actually try the formula that Dave gave you? i.e.: =SUMPRODUCT((LEFT(C11:$C$65536,1)="I")*(F11:$F$655 36=F10)*(E11:$E $65536)) His comment meant that you don't use wildcards like that in SP - instead you can use the LEFT function. Pete On Feb 2, 4:11*pm, Excel Nut wrote: Here's a sample of my data (if you want to copy and parse it)... Row|Column C|Column D|Column E|Column F Row 1|J0000000042900|JAN|8.64|ALLOC FAX Row 2|I0000000299000|JAN|18.94|ALLOC FAX Row 3|I3001195880030|JAN|1.25|ALLOC FAX Row 4|I3001310880030|JAN|17.69|ALLOC FAX Row 5|J0101170880030|JAN|2.86|ALLOC FAX Row 6|J0101175880030|JAN|0.11|ALLOC FAX Row 7|J0101180880030|JAN|5.67|ALLOC FAX Row 8|J0000000042900|JAN|30.29|ALLOC PHONE Row 9|I0000000299000|JAN|28.35|ALLOC PHONE Row 10|I3001195880040|JAN|0.25|ALLOC PHONE Row 11|I3001195880040|JAN|1.39|ALLOC PHONE Row 12|I3001196880040|JAN|2.97|ALLOC PHONE Row 13|I3001210880040|JAN|4.99|ALLOC PHONE Row 14|I3001310880040|JAN|18.75|ALLOC PHONE Row 15|J0101170880040|JAN|23.65|ALLOC PHONE Row 16|J0101175880040|JAN|2.84|ALLOC PHONE Row 17|J0101180880040|JAN|1.85|ALLOC PHONE Row 18|J0101185880040|JAN|1.95|ALLOC PHONE I want a formula for cell E1 that will evaluate all rows below the formula row that have values in Column C starting wiht "I" and have values in column F that match the value in F1. I want to be able to copy the same formula to E9 and 58 other cells further down, This is the formula I tried but Dave said I cannot use wildcards. =SUMPRODUCT((C2:$C$3000="I*")*(F2:$F$3000=F1)*(E2: $E$3000)) I know I can use a SUMIF formula to sum all column C values starting with "I". =SUMIF(C1:C7,"I*",E1:E7) But I also need to match values in column F with the value in column F on the same row as the formula. If I could get this to work, I could copy the same formula down for all sixty I0000000299000 totals without needing to change the cell references, otherwise I will need to use 60 SUMIF formulas with different references for each one. Thanks for looking at this- Hide quoted text - - Show quoted text - Sorry. I didn't notice that he had suggested a new formula. I thought he had just put my original formula above hhis explanation of why it wasn't working. Yes, I have since tried Dave's formula and it works fine, except that I had to modify it to refer just to the cells containing data. I had originally allowed for an extra 1000 or so rows below my data to accommodate new data to be added in the future. However, when I include a reference to these blank cells in my SUMPRODUCT formula, I get a #VALUE! error. Any way around that? Excel Nut Thanks Dave and Pete.- Hide quoted text - - Show quoted text - Instead of using checks for extra data, etc... why don't you use 'Dynamic orranges' (http://www.contextures.com/xlNames01.html#Dynamic) -- Pecoflyer Cheers - *'Membership is free' (http://www.thecodecage.com)* & allows file upload -faster and better answers *Adding your XL version* to your post helps finding solution faster ------------------------------------------------------------------------ Pecoflyer's Profile: http://www.thecodecage.com/forumz/member.php?userid=14 View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=57460 |
#14
Posted to microsoft.public.excel.misc
|
|||
|
|||
What's Wrong With This Formula?
try removing the wildcard like this
=SUMPRODUCT((LEFT(C3:$C$60,1)="I")*(F3:$F$60=F2)*( E3:$E$60)) it should do what you want and if not pls send me your workbook pls take a moment to click on the Yes button as your feedback to this post. thanks -- pls click on the Yes button below if this posting is helpful. Thank You cheers, francis "Excel Nut" wrote: On Feb 2, 2:43 pm, xlmate wrote: Hi try the formula provided by Dave, Adjust the range to suit yours and the row numbers to your dataset as the recalculation would take some time if you referenced all the rows in your spreadsheet Post back if its don't work citing the error you receive and the formula you use. HTH Pls provide your feedback -- Pls click on the Yes button below if this posting is helpful. Thank You cheers, francis "Excel Nut" wrote: On Feb 2, 1:11 pm, Excel Nut wrote: On Feb 2, 11:52 am, Pecoflyer wrote: Excel Nut;209408 Wrote: On Feb 2, 10:32*am, Dave Peterson wrote: =SUMPRODUCT((LEFT(C11:$C$65536,1)="I")*(F11:$F$655 36=F10)*(E11:$E$65536)) (You can't use wildcards like that.) As an aside, if you need all those rows, fine. *But your formula will recalculate much quicker if you limit the range to what you need (or a little more for safety???). Excel Nut wrote: What's Wrong With This Formula? =SUMPRODUCT((C11:$C$65536="I*")*(F11:$F$65536=F10) *(E11:$E$65536)) - I'm using Excel 2000 - Column C contains text values - Column F contains text values - Column E contains numeric values - My formula is in cell E10 - I want to sum cells in column E below the row containing this formula using the following criteria... - This formula is returning 0 Criteria: 1) cells in column C whose text values start with "I" 2) cells in column F whose text values equals the text value in cell F10 Note: - The purpose of the mixed references (i.e. relative/absolute) is because I need to copy this formula down but it should just evaluate cells below the row containing the formula. - I've tried using F11 instead of F10 but I'm still returning 0 -- Dave Peterson- Hide quoted text - - Show quoted text - Darn! Wildcards worked in my SUMIF formulas but this one I have two criteria and that's why I used SUMPRODUCT. Why do you need the wildcard ? -- Pecoflyer Cheers - *'Membership is free' (http://www.thecodecage.com)*&allowsfile upload -faster and better answers *Adding your XL version* to your post helps finding solution faster ------------------------------------------------------------------------ Pecoflyer's Profile:http://www.thecodecage.com/forumz/member.php?userid=14 View this thread:http://www.thecodecage.com/forumz/sh...460-Hidequoted text - - Show quoted text - Here's a sample of my data (if you want to copy and parse it)... Row|Column C|Column D|Column E|Column F Row 1|J0000000042900|JAN|8.64|ALLOC FAX Row 2|I0000000299000|JAN|18.94|ALLOC FAX Row 3|I3001195880030|JAN|1.25|ALLOC FAX Row 4|I3001310880030|JAN|17.69|ALLOC FAX Row 5|J0101170880030|JAN|2.86|ALLOC FAX Row 6|J0101175880030|JAN|0.11|ALLOC FAX Row 7|J0101180880030|JAN|5.67|ALLOC FAX Row 8|J0000000042900|JAN|30.29|ALLOC PHONE Row 9|I0000000299000|JAN|28.35|ALLOC PHONE Row 10|I3001195880040|JAN|0.25|ALLOC PHONE Row 11|I3001195880040|JAN|1.39|ALLOC PHONE Row 12|I3001196880040|JAN|2.97|ALLOC PHONE Row 13|I3001210880040|JAN|4.99|ALLOC PHONE Row 14|I3001310880040|JAN|18.75|ALLOC PHONE Row 15|J0101170880040|JAN|23.65|ALLOC PHONE Row 16|J0101175880040|JAN|2.84|ALLOC PHONE Row 17|J0101180880040|JAN|1.85|ALLOC PHONE Row 18|J0101185880040|JAN|1.95|ALLOC PHONE I want a formula for cell E1 that will evaluate all rows below the formula row that have values in Column C starting wiht "I" and have values in column F that match the value in F1. I want to be able to copy the same formula to E9 and 58 other cells further down, This is the formula I tried but Dave said I cannot use wildcards. =SUMPRODUCT((C2:$C$3000="I*")*(F2:$F$3000=F1)*(E2: $E$3000)) I know I can use a SUMIF formula to sum all column C values starting with "I". =SUMIF(C1:C7,"I*",E1:E7) But I also need to match values in column F with the value in column F on the same row as the formula. If I could get this to work, I could copy the same formula down for all sixty I0000000299000 totals without needing to change the cell references, otherwise I will need to use 60 SUMIF formulas with different references for each one. Thanks for looking at this.- Hide quoted text - - Show quoted text - Here's a sample of my data (if you want to copy and parse it)... Row|Column C|Column D|Column E|Column F Row 1|J0000000042900|JAN|8.64|ALLOC FAX Row 2|I0000000299000|JAN|18.94|ALLOC FAX Row 3|I3001195880030|JAN|1.25|ALLOC FAX Row 4|I3001310880030|JAN|17.69|ALLOC FAX Row 5|J0101170880030|JAN|2.86|ALLOC FAX Row 6|J0101175880030|JAN|0.11|ALLOC FAX Row 7|J0101180880030|JAN|5.67|ALLOC FAX Row 8|J0000000042900|JAN|30.29|ALLOC PHONE Row 9|I0000000299000|JAN|28.35|ALLOC PHONE Row 10|I3001195880040|JAN|0.25|ALLOC PHONE Row 11|I3001195880040|JAN|1.39|ALLOC PHONE Row 12|I3001196880040|JAN|2.97|ALLOC PHONE Row 13|I3001210880040|JAN|4.99|ALLOC PHONE Row 14|I3001310880040|JAN|18.75|ALLOC PHONE Row 15|J0101170880040|JAN|23.65|ALLOC PHONE Row 16|J0101175880040|JAN|2.84|ALLOC PHONE Row 17|J0101180880040|JAN|1.85|ALLOC PHONE Row 18|J0101185880040|JAN|1.95|ALLOC PHONE I want a formula for cell E2 that will evaluate all rows below the formula row that have values in Column C starting with "I" and have values in column F that match the value in F2. I want to be able to copy the same formula to E9 and 58 other cells further down, This is the formula I tried but Dave said I cannot use wildcards. =SUMPRODUCT((C3:$C$3000="I*")*(F3:$F$3000=F2)*(E3: $E$3000)) I know I can use a SUMIF formula to sum all column C values starting with "I". =SUMIF(C3:C7,"I*",E3:E7) But I also need to match values in column F with the value in column F on the same row as the formula. If I could get this to work, I could copy the same formula down for all sixty I0000000299000 totals without needing to change the cell references, otherwise I will need to use 60 SUMIF formulas with different references for each one. Thanks for looking at this. Excel Nut- Hide quoted text - - Show quoted text - =SUMPRODUCT((C3:$C$3000="I*")*(F3:$F$3000=F2)*(E3: $E$3000)) I have adjusted the formula so that it doesn't refer to ALL rows. Currently my data goes down only to row 1541 but I need to reference some extra rows below my current data to allow for additional data to be added in the future. But when the SUMPRODUCT formula refers to these empty rows it returns a #VALUE! error. Any way around that? |
#15
Posted to microsoft.public.excel.misc
|
|||
|
|||
What's Wrong With This Formula?
First, you still can't use wild cards this way:
=SUMPRODUCT((C3:$C$3000="I*")*(F3:$F$3000=F2)*(E3: $E$3000)) Second... Do you have any #value! errors in any of those ranges? If so, clean up those errors. Do you have any non-numeric data in E3:E3000? If you have text in column E, then the syntax you used (multiplying the true/falses by text) will fail. You could try: =SUMPRODUCT(--(LEFT(C11:$C$65536,1)="I"),--(F3:$F$3000=F2),(E3:$E$3000)) 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 he http://www.xldynamic.com/source/xld.SUMPRODUCT.html And J.E. McGimpsey has some notes at: http://mcgimpsey.com/excel/formulae/doubleneg.html Excel Nut wrote: <<snipped - Show quoted text - =SUMPRODUCT((C3:$C$3000="I*")*(F3:$F$3000=F2)*(E3: $E$3000)) I have adjusted the formula so that it doesn't refer to ALL rows. Currently my data goes down only to row 1541 but I need to reference some extra rows below my current data to allow for additional data to be added in the future. But when the SUMPRODUCT formula refers to these empty rows it returns a #VALUE! error. Any way around that? -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Insert Calculated Field (wrong Qty*Price = wrong Amount) | Excel Discussion (Misc queries) | |||
What is wrong with this formula? | Excel Worksheet Functions | |||
What is wrong with formula? | Excel Worksheet Functions | |||
What's wrong with this formula? | Excel Discussion (Misc queries) | |||
Help please,what is wrong with this formula? | New Users to Excel |