| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
SUMPRODUCT Problem
First, thank you in advance for your help and looking at this.
I'm trying to use this formula to sum the values in cells D4:D4650 based on the criteria that the date in cells B4:B4650 is from 2006 and it has not been rejected (E4:E4650). =SUMPRODUCT(--(B4:B4650=YEAR(2006)),--(E4:E4650<"Rejected"),(D4:D4650)) The problem with this formula is that it returns $0.00 and it should be over $5m. TIA for your help Joe |
|
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
SUMPRODUCT Problem
Try:
=SUMPRODUCT(--(YEAR(B4:B4650)=2006),--(E4:E4650<"Rejected"),D4:D4650) In article , Joe Gieder wrote: First, thank you in advance for your help and looking at this. I'm trying to use this formula to sum the values in cells D4:D4650 based on the criteria that the date in cells B4:B4650 is from 2006 and it has not been rejected (E4:E4650). =SUMPRODUCT(--(B4:B4650=YEAR(2006)),--(E4:E4650<"Rejected"),(D4:D4650)) The problem with this formula is that it returns $0.00 and it should be over $5m. TIA for your help Joe |
|
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
SUMPRODUCT Problem
Your fix worked great thank you. I just need to learn how to write the order
of the formula. I have one question though, if I may impose. Within the range of B4:B4650 there are numerous cells that have text, when I deleted the text the formula worked but with the text it did not. Is there a way to leave the text and still have it work? Thank you again for your help. Joe "JE McGimpsey" wrote: Try: =SUMPRODUCT(--(YEAR(B4:B4650)=2006),--(E4:E4650<"Rejected"),D4:D4650) In article , Joe Gieder wrote: First, thank you in advance for your help and looking at this. I'm trying to use this formula to sum the values in cells D4:D4650 based on the criteria that the date in cells B4:B4650 is from 2006 and it has not been rejected (E4:E4650). =SUMPRODUCT(--(B4:B4650=YEAR(2006)),--(E4:E4650<"Rejected"),(D4:D4650)) The problem with this formula is that it returns $0.00 and it should be over $5m. TIA for your help Joe |
|
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
SUMPRODUCT Problem
Try this:
=SUMPRODUCT(--(TEXT(B4:B4650,"yyyy")="2006"),--(E4:E4650<"Rejected"),D4:D4650) Biff "Joe Gieder" wrote in message ... Your fix worked great thank you. I just need to learn how to write the order of the formula. I have one question though, if I may impose. Within the range of B4:B4650 there are numerous cells that have text, when I deleted the text the formula worked but with the text it did not. Is there a way to leave the text and still have it work? Thank you again for your help. Joe "JE McGimpsey" wrote: Try: =SUMPRODUCT(--(YEAR(B4:B4650)=2006),--(E4:E4650<"Rejected"),D4:D4650) In article , Joe Gieder wrote: First, thank you in advance for your help and looking at this. I'm trying to use this formula to sum the values in cells D4:D4650 based on the criteria that the date in cells B4:B4650 is from 2006 and it has not been rejected (E4:E4650). =SUMPRODUCT(--(B4:B4650=YEAR(2006)),--(E4:E4650<"Rejected"),(D4:D4650)) The problem with this formula is that it returns $0.00 and it should be over $5m. TIA for your help Joe |
|
#5
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
SUMPRODUCT Problem
Worked perfectly. Thank you for the help.
"T. Valko" wrote: Try this: =SUMPRODUCT(--(TEXT(B4:B4650,"yyyy")="2006"),--(E4:E4650<"Rejected"),D4:D4650) Biff "Joe Gieder" wrote in message ... Your fix worked great thank you. I just need to learn how to write the order of the formula. I have one question though, if I may impose. Within the range of B4:B4650 there are numerous cells that have text, when I deleted the text the formula worked but with the text it did not. Is there a way to leave the text and still have it work? Thank you again for your help. Joe "JE McGimpsey" wrote: Try: =SUMPRODUCT(--(YEAR(B4:B4650)=2006),--(E4:E4650<"Rejected"),D4:D4650) In article , Joe Gieder wrote: First, thank you in advance for your help and looking at this. I'm trying to use this formula to sum the values in cells D4:D4650 based on the criteria that the date in cells B4:B4650 is from 2006 and it has not been rejected (E4:E4650). =SUMPRODUCT(--(B4:B4650=YEAR(2006)),--(E4:E4650<"Rejected"),(D4:D4650)) The problem with this formula is that it returns $0.00 and it should be over $5m. TIA for your help Joe |
|
#6
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
SUMPRODUCT Problem
You're welcome. Thanks for the quick feedback!
Biff "Joe Gieder" wrote in message ... Worked perfectly. Thank you for the help. "T. Valko" wrote: Try this: =SUMPRODUCT(--(TEXT(B4:B4650,"yyyy")="2006"),--(E4:E4650<"Rejected"),D4:D4650) Biff "Joe Gieder" wrote in message ... Your fix worked great thank you. I just need to learn how to write the order of the formula. I have one question though, if I may impose. Within the range of B4:B4650 there are numerous cells that have text, when I deleted the text the formula worked but with the text it did not. Is there a way to leave the text and still have it work? Thank you again for your help. Joe "JE McGimpsey" wrote: Try: =SUMPRODUCT(--(YEAR(B4:B4650)=2006),--(E4:E4650<"Rejected"),D4:D4650) In article , Joe Gieder wrote: First, thank you in advance for your help and looking at this. I'm trying to use this formula to sum the values in cells D4:D4650 based on the criteria that the date in cells B4:B4650 is from 2006 and it has not been rejected (E4:E4650). =SUMPRODUCT(--(B4:B4650=YEAR(2006)),--(E4:E4650<"Rejected"),(D4:D4650)) The problem with this formula is that it returns $0.00 and it should be over $5m. TIA for your help Joe |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Sumproduct Problem | Excel Worksheet Functions | |||
| sumproduct problem | Excel Worksheet Functions | |||
| Sumproduct Problem | Excel Worksheet Functions | |||
| sumproduct problem | Excel Worksheet Functions | |||
| Another Sumproduct & #N/A problem | Excel Worksheet Functions | |||
Linear Mode
