Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Nested formula Value error
I have a spreadsheet with sales leads assigned to each salesperson. Several
years ago I created a summary including a formula to count how many leads each salesperson gets per month: =SUM(IF($A$3:$A$2870="Jan",IF($L$3:$L$2870="Joe",1 ,0),0)). A is the date column, L is the salesperson column. I added a new salesperson and can't get the formula to work! If I copy, paste and change the "Joe" to "Luke" I get #VALUE!. If I type the formula with the new name I get the same #VALUE!. If I type the formula & highlight the areas, same deal. I tried the function wizard but it isn't producing the formula. Help! Is there a way to copy or re-write this formula for the new guy and make it work? Beth |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Nested formula Value error
That is an array-entered formula... after editing it, you need to commit the
function using Ctrl+Shift+Enter, not just Enter by itself. -- Rick (MVP - Excel) "Beth" wrote in message ... I have a spreadsheet with sales leads assigned to each salesperson. Several years ago I created a summary including a formula to count how many leads each salesperson gets per month: =SUM(IF($A$3:$A$2870="Jan",IF($L$3:$L$2870="Joe",1 ,0),0)). A is the date column, L is the salesperson column. I added a new salesperson and can't get the formula to work! If I copy, paste and change the "Joe" to "Luke" I get #VALUE!. If I type the formula with the new name I get the same #VALUE!. If I type the formula & highlight the areas, same deal. I tried the function wizard but it isn't producing the formula. Help! Is there a way to copy or re-write this formula for the new guy and make it work? Beth |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Nested formula Value error
This is an array formula.
Are you hitting ctrl-shift-enter when you're done making your change? This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it correctly, excel will wrap curly brackets {} around your formula. (don't type them yourself.) Adjust the range to match--but you can only use the whole column in xl2007. Beth wrote: I have a spreadsheet with sales leads assigned to each salesperson. Several years ago I created a summary including a formula to count how many leads each salesperson gets per month: =SUM(IF($A$3:$A$2870="Jan",IF($L$3:$L$2870="Joe",1 ,0),0)). A is the date column, L is the salesperson column. I added a new salesperson and can't get the formula to work! If I copy, paste and change the "Joe" to "Luke" I get #VALUE!. If I type the formula with the new name I get the same #VALUE!. If I type the formula & highlight the areas, same deal. I tried the function wizard but it isn't producing the formula. Help! Is there a way to copy or re-write this formula for the new guy and make it work? Beth -- Dave Peterson |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Nested formula Value error
Perfect! You're both Geniuses! Thx a ton!
"Beth" wrote: I have a spreadsheet with sales leads assigned to each salesperson. Several years ago I created a summary including a formula to count how many leads each salesperson gets per month: =SUM(IF($A$3:$A$2870="Jan",IF($L$3:$L$2870="Joe",1 ,0),0)). A is the date column, L is the salesperson column. I added a new salesperson and can't get the formula to work! If I copy, paste and change the "Joe" to "Luke" I get #VALUE!. If I type the formula with the new name I get the same #VALUE!. If I type the formula & highlight the areas, same deal. I tried the function wizard but it isn't producing the formula. Help! Is there a way to copy or re-write this formula for the new guy and make it work? Beth |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Nested formula Value error
You may want an alternative formula:
=sumproduct(--($a$3:$a$2870="jan"),--($l$3:$l$2870="joe")) 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 And if you're using xl2007, look in Excel's help for =countifs(). Beth wrote: I have a spreadsheet with sales leads assigned to each salesperson. Several years ago I created a summary including a formula to count how many leads each salesperson gets per month: =SUM(IF($A$3:$A$2870="Jan",IF($L$3:$L$2870="Joe",1 ,0),0)). A is the date column, L is the salesperson column. I added a new salesperson and can't get the formula to work! If I copy, paste and change the "Joe" to "Luke" I get #VALUE!. If I type the formula with the new name I get the same #VALUE!. If I type the formula & highlight the areas, same deal. I tried the function wizard but it isn't producing the formula. Help! Is there a way to copy or re-write this formula for the new guy and make it work? Beth -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Nested IF error | Excel Worksheet Functions | |||
Nested IF error | Excel Worksheet Functions | |||
3 nested IFs causes error | Excel Worksheet Functions | |||
Nested IF error | Excel Worksheet Functions | |||
I get error with "ROWS" in the formula - nested formula question | Excel Worksheet Functions |