Sum and Count on a criteria range

[Swifey]

I want to create a summary of how many card holders have spent over a certain amount and the sum of sales value. Is there a formula that will count and sum certain criterias.

for example my data is as follows
Card_Holder,,,Sales Value
0000159309,,,297974.34
0000500363,,,255251.28
0000158760,,,152886.95
0000159430,,,113783.17
0000159409,,,91722.89
0000159784,,,56038.08
0000159251,,,55920.18
0000159097,,,55809.52
0000158398,,,47314.29
0000157188,,,41827.19
0000156659,,,36249.22
0000157849,,,30939.84
0000158397,,,27791.01
0000162057,,,26113.84
0000160475,,,25757.16
0000158776,,,25498.18

Example of Summary I want to produce
Spend Range,,,,,,,,,,,,,,,,,,,,,,,,,,,,No,,,,,,,,,,,Value
Spend £100,000 plus,,,,,,,,,,,,,,,,,4,,,,,,,,,,,,,819895.74
Spend £50k - £99,999k,,,,,,,,,,,,,4,,,,,,,,,,,,,259490.67
Spend £25k - £49,999k,,,,,,,,,,,,,8,,,,,,,,,,,,,261490.73


Thank you
Swifey

 

[nevillestoke]

Take a look at the Sumif and Countif functions. These should produce the results you are looking for.

 

[Swifey]

How do I set the criteria into the sumif/countif

Sorry
Swifey

 

[Mark W.]

PivotTable approach with equal intervals...

Book1
	A	B	C	D	E	F	G	H
1	Card_Holder	SalesValue			Data
2	0000159309	297974.34		SalesValue	No.	Value
3	0000500363	255251.28		>100000	4	819895.74
4	0000158760	152886.95		75000-100000	1	91722.89
5	0000159430	113783.17		50000-75000	3	167767.78
6	0000159409	91722.89		25000-50000	8	261490.73
7	0000159784	56038.08		GrandTotal	16	1340877.14
8	0000159251	55920.18
9	0000159097	55809.52
10	0000158398	47314.29
11	0000157188	41827.19
12	0000156659	36249.22
13	0000157849	30939.84
14	0000158397	27791.01
15	0000162057	26113.84
16	0000160475	25757.16
17	0000158776	25498.18
18
Sheet1

 

[Swifey]

You must think I am really thick but where do I define a range in a pivot table

Swifey

 

[Mark W.]

You must think I am really thick but where do I define a range in a pivot table

Swifey

At Step 2 of 3 in the PivotTable Wizard.

Or, do you mean the grouping interval? Right click on the PivotTable's Sale Value field and choose "Group and Show Detail" | Group from the popup menu.

I used 25000, 99999, 25000 respectively.

 

[Swifey]

PivotWizard 2 of 3 asks "Where is the data you want to use?" I'm using windows XP will this have any knock on effect?

Swifey

 

[Aladin Akyurek]

I want to create a summary of how many card holders have spent over a certain amount and the sum of sales value. Is there a formula that will count and sum certain criterias...

Book1
	A	B	C	D	E	F	G	H
1	Card_Holder		SalesValue		SpendRange	Count	Total
2	0000159309		297974.34		25,000	8	261490.7
3	0000500363		255251.28		50,000	4	259490.7
4	0000158760		152886.95		100,000	4	819895.7
5	0000159430		113783.17		1.00E+308
6	0000159409		91722.89
7	0000159784		56038.08
8	0000159251		55920.18
9	0000159097		55809.52
10	0000158398		47314.29
11	0000157188		41827.19
12	0000156659		36249.22
13	0000157849		30939.84
14	0000158397		27791.01
15	0000162057		26113.84
16	0000160475		25757.16
17	0000158776		25498.18
Sheet1

The formulas...

F2:

=SUMPRODUCT(($C$2:$C$17>=E2)*($C$2:$C$17<E3))

G2:

=SUMPRODUCT(($C$2:$C$17>=E2)*($C$2:$C$17<E3),$C$2:$C$17)

 

[Mark W.]

...any knock on effect?

Swifey

Not familiar with this phrase. Did you see my ammended posting above?

 

[Swifey]

No sorry - I'll try it