Using FREQUENCY to sum instead of count

Hi Guys,

I wonder if anyone can help me. I've been using the FREQUENCY equation for a good while and absolutely love the time it saves me. Recently I have been using it to count the number of customers that fall into a particular spend bracket and it works brilliantly!
However I now need to sum the revenue within the spend bracket instead of just counting how many occurences there are. I've tried lots of variations on a SUMIF but I'm not getting anywhere.
Does anyone know of a way I can do this? (sample data below)
All help is much appreciated!

Many Thanks

Emma

Bins Frequency
200 3
500 1
1000 4
5000 4
10000 1
20000 0
50000 1

Data
Acc Num Rev I want to Sum
308061227 £ 57,396
105095006 £ 25,247
103491004 £ 5,844
103895002 £ 2,669
104130003 £ 2,365
308097412 £ 2,307
102806009 £ 2,138
103496009 £ 970
101335007 £ 700
100012008 £ 558
308713382 £ 507
100012008 £ 217
109014007 £ 162
109014007 £ 162
100017003 £ 0 -

Originally Posted by Emma_Fairclough

Hi Guys,

I wonder if anyone can help me. I've been using the FREQUENCY equation for a good while and absolutely love the time it saves me. Recently I have been using it to count the number of customers that fall into a particular spend bracket and it works brilliantly!
However I now need to sum the revenue within the spend bracket instead of just counting how many occurences there are. I've tried lots of variations on a SUMIF but I'm not getting anywhere.
Does anyone know of a way I can do this? (sample data below)
All help is much appreciated!

Many Thanks

Emma

Bins Frequency
200 3
500 1
1000 4
5000 4
10000 1
20000 0
50000 1

Data
Acc Num Rev I want to Sum
308061227 £ 57,396

snipped
-

Hi,

With your Data in A12:C26, (I simply parsed your original table and the '£s" are in column B and the values in column C), and the Bins in A2:A8, enter the following into C2 and copy down.

Please Login or Register  to view this content.

If you like the FREQUENCY() function, just wait until you've got into all the permutations and composite functions that SUMPRODUCT offers.

It's being used in some quite original forms, and in ways I suspect that not even Microsoft envisaged.

Rgds