Help with a counta and if statement

[goldcat]

this is my sheet:

Athletic Club Balances.xls
	AN	AO	AP	AQ	AR	AS	AT	AU	AV	AW	AX	AY	AZ
1	2,448.32				2,196.11				1,787.88
2
3	Balance				Balance				Balance
4	Oct-03		Pmt	Fees	Nov-03		Pmt	Fees	Dec-03			totals	pre-pmts
5
6	0.00				0.00				0.00			0.00	0
7	42.75		x		42.75		x		42.75			-213.73	5
8	0.00				0.00				0.00			0.00	0
9	0.00				0.00				0.00			0.00	0
10	42.75				0.00				0.00			-128.24	3
2003

as you can see in column AZ I am trying to sum based on text entered in other columns.

I need to adjust the subtractive portion sothat it only subtracts if the value is X.

any help would be appreciated

thanks W.

 

[Andrew Poulsom]

Would the x's be in the Fees columns? That's how it looks from your formula.

 

[goldcat]

yes !
What I'm trying to do is keep track of who has made prepayments.
the thig is we also let them freeze the membership for periods of time.

If I enter X in the fees then it subtracts from the total prepayment, but if I enter F then it does not.

does that make any sense

 

[Andrew Poulsom]

Maybe this will help:

BOOK4
	A	B	C	D	E	F	G	H
1	Data						Totals
2	Pmt	Fees	Pmt	Fees	Pmt	Fees	Pmt	Fees
3	x	x		x			1	2
Sheet1

The formulas in G3 and H3 are:

=SUMPRODUCT((A2:F2=G2)*(A3:F3="x"))
=SUMPRODUCT((A2:F2=H2)*(A3:F3="x"))

In those formulas the references to G2 and H2 could be replaced with "Pmt" and "Fees" respectively.

 

[goldcat]

thanks Andrew

that is what I needed and they look a lot tidier than mine did

ray: ray:

 

[goldcat]

Athletic Club Balances.xls
	AR	AS	AT	AU	AV	AW	AX	AY	AZ	BA	BB	BC
3	Balance				Balance
4	Nov-03		Pmt	Fees	Dec-03			totals	pre-pmts	payment left	pmt	fees
5
6	0.00		x	f	42.75					1	1	0
7	42.75		x	x	42.75					4	5	1
Sheet1

with the couple of changes it does just what I wanted. .... thanks again

ray: ray: ray: ray: ray: