Finding New Contributions

[Jack Taylor]

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
D4766 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

 

[T. Valko]

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:A766>0)+(B4:B766>0)+(C4:C766>0)+(D4766>0)>0),--((E4:E766>0)+(F4:F766>0)=0),--(G4:G766>0))

For the sum:

=SUMPRODUCT(--((A4:A766>0)+(B4:B766>0)+(C4:C766>0)+(D4766>0)>0),--((E4:E766>0)+(F4:F766>0)=0),G4:G766)

Biff

 

[David Biddulph]

In H4 use the formula =IF(AND(G4>0,SUM(E4:F4)=0),G4,"")
Then SUM that column.

 

[Lori]

Assuming all contributions in range are positive or blank try these:

=SUMPRODUCT((MMULT(--(A4:G766>0),{1;1;1;1;-5;-5;5})>5))

=SUMPRODUCT((MMULT(--(A4:G766>0),{1;1;1;1;-5;-5;5})>5)*G4:G766)

 

[Lori]

First formula should have *1 before last parenthesis.