| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Finding New Contributions
Is there a formula that will do the following:
Where... A4:A766 contain contributions made in 2001 by individuals B4:B766 contain contributions made in 2002 by individuals C4:C766 contain contributions made in 2003 by individuals D4:D766 contain contributions made in 2004 by individuals E4:E766 contain contributions made in 2005 by individuals F4:F766 contain contributions made in 2006 by individuals G4:G766 contain pledges made for next year by individuals Need to count the number of individuals who have PLEDGED FOR 2007 but have NOT CONTRIBUTED SINCE AT LEAST 2004...EARLIER IF NECESSARY. Example: If a person has a recorded pledge in cells G4:G766, fomula needs to look back at all previous years and count all persons if their last contribution was in 2004. If these person's last contributions was in 2003 and nothing since, they should also be counted. If their last contributions were in 2002 and nothing since, they should be counted...if in 2001 and nothing since, they should be counted. If any person's last contribution was in 2005 or 2006, they should NOT be counted. Also need a formula that SUMS all 2007 pledge dollars based upon this same criteria. Whew! Any ideas? JT |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Finding New Contributions
If a person DIDN'T contribute I'm assuming the cell is empty or maybe
contains a 0? If I understood you. Try this: For the count: =SUMPRODUCT(--((A4:A7660)+(B4:B7660)+(C4:C7660)+(D4:D7660)0 ),--((E4:E7660)+(F4:F7660)=0),--(G4:G7660)) For the sum: =SUMPRODUCT(--((A4:A7660)+(B4:B7660)+(C4:C7660)+(D4:D7660)0 ),--((E4:E7660)+(F4:F7660)=0),G4:G766) Biff "Jack Taylor" wrote in message ... Is there a formula that will do the following: Where... A4:A766 contain contributions made in 2001 by individuals B4:B766 contain contributions made in 2002 by individuals C4:C766 contain contributions made in 2003 by individuals D4:D766 contain contributions made in 2004 by individuals E4:E766 contain contributions made in 2005 by individuals F4:F766 contain contributions made in 2006 by individuals G4:G766 contain pledges made for next year by individuals Need to count the number of individuals who have PLEDGED FOR 2007 but have NOT CONTRIBUTED SINCE AT LEAST 2004...EARLIER IF NECESSARY. Example: If a person has a recorded pledge in cells G4:G766, fomula needs to look back at all previous years and count all persons if their last contribution was in 2004. If these person's last contributions was in 2003 and nothing since, they should also be counted. If their last contributions were in 2002 and nothing since, they should be counted...if in 2001 and nothing since, they should be counted. If any person's last contribution was in 2005 or 2006, they should NOT be counted. Also need a formula that SUMS all 2007 pledge dollars based upon this same criteria. Whew! Any ideas? JT |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Finding New Contributions
In H4 use the formula =IF(AND(G40,SUM(E4:F4)=0),G4,"")
Then SUM that column. -- David Biddulph "Jack Taylor" wrote in message ... Is there a formula that will do the following: Where... A4:A766 contain contributions made in 2001 by individuals B4:B766 contain contributions made in 2002 by individuals C4:C766 contain contributions made in 2003 by individuals D4:D766 contain contributions made in 2004 by individuals E4:E766 contain contributions made in 2005 by individuals F4:F766 contain contributions made in 2006 by individuals G4:G766 contain pledges made for next year by individuals Need to count the number of individuals who have PLEDGED FOR 2007 but have NOT CONTRIBUTED SINCE AT LEAST 2004...EARLIER IF NECESSARY. Example: If a person has a recorded pledge in cells G4:G766, fomula needs to look back at all previous years and count all persons if their last contribution was in 2004. If these person's last contributions was in 2003 and nothing since, they should also be counted. If their last contributions were in 2002 and nothing since, they should be counted...if in 2001 and nothing since, they should be counted. If any person's last contribution was in 2005 or 2006, they should NOT be counted. Also need a formula that SUMS all 2007 pledge dollars based upon this same criteria. Whew! Any ideas? JT |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Finding New Contributions
Assuming all contributions in range are positive or blank try these:
=SUMPRODUCT((MMULT(--(A4:G7660),{1;1;1;1;-5;-5;5})5)) =SUMPRODUCT((MMULT(--(A4:G7660),{1;1;1;1;-5;-5;5})5)*G4:G766) Jack Taylor wrote: Is there a formula that will do the following: Where... A4:A766 contain contributions made in 2001 by individuals B4:B766 contain contributions made in 2002 by individuals C4:C766 contain contributions made in 2003 by individuals D4:D766 contain contributions made in 2004 by individuals E4:E766 contain contributions made in 2005 by individuals F4:F766 contain contributions made in 2006 by individuals G4:G766 contain pledges made for next year by individuals Need to count the number of individuals who have PLEDGED FOR 2007 but have NOT CONTRIBUTED SINCE AT LEAST 2004...EARLIER IF NECESSARY. Example: If a person has a recorded pledge in cells G4:G766, fomula needs to look back at all previous years and count all persons if their last contribution was in 2004. If these person's last contributions was in 2003 and nothing since, they should also be counted. If their last contributions were in 2002 and nothing since, they should be counted...if in 2001 and nothing since, they should be counted. If any person's last contribution was in 2005 or 2006, they should NOT be counted. Also need a formula that SUMS all 2007 pledge dollars based upon this same criteria. Whew! Any ideas? JT |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Finding New Contributions
First formula should have *1 before last parenthesis.
Lori wrote: Assuming all contributions in range are positive or blank try these: =SUMPRODUCT((MMULT(--(A4:G7660),{1;1;1;1;-5;-5;5})5)) =SUMPRODUCT((MMULT(--(A4:G7660),{1;1;1;1;-5;-5;5})5)*G4:G766) Jack Taylor wrote: Is there a formula that will do the following: Where... A4:A766 contain contributions made in 2001 by individuals B4:B766 contain contributions made in 2002 by individuals C4:C766 contain contributions made in 2003 by individuals D4:D766 contain contributions made in 2004 by individuals E4:E766 contain contributions made in 2005 by individuals F4:F766 contain contributions made in 2006 by individuals G4:G766 contain pledges made for next year by individuals Need to count the number of individuals who have PLEDGED FOR 2007 but have NOT CONTRIBUTED SINCE AT LEAST 2004...EARLIER IF NECESSARY. Example: If a person has a recorded pledge in cells G4:G766, fomula needs to look back at all previous years and count all persons if their last contribution was in 2004. If these person's last contributions was in 2003 and nothing since, they should also be counted. If their last contributions were in 2002 and nothing since, they should be counted...if in 2001 and nothing since, they should be counted. If any person's last contribution was in 2005 or 2006, they should NOT be counted. Also need a formula that SUMS all 2007 pledge dollars based upon this same criteria. Whew! Any ideas? JT |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Finding a link source. | Excel Worksheet Functions | |||
| Finding Asymptotes from a set of data in Excel | Excel Worksheet Functions | |||
| Finding Asymptotes from a set of data in Excel | Excel Discussion (Misc queries) | |||
| Help needed in finding specific series via row. | Excel Worksheet Functions | |||
| Finding Duplicate Names from Different Lists... | Excel Discussion (Misc queries) | |||
Linear Mode
