Help on ifsum

[Guest]

I have a spreadsheet that does the following on a weekly basic,

First Name Surname Total SPH Sales £ Attendance
%
Claire Jepson 32.00 0.37 10 £40.00 100
David Thomas 33.25 0.51 17 £85.00 100


What I would like is that where it has pound sign to auto sum from the
following table, (SECOND TABLE)

SPH
Attendance % 0.24 0.25-0.35 0.36-0.49 0.50-0.69 0.70+
20%
21-39% £0.30 £0.40
41-59% £0.40 £0.50
60-79% £0.30 £0.40 £0.60 £0.75
80-89% £0.50 £0.75 £1.00 £2.00
90-99% £1.50 £2.00 £2.50 £5.00
100% £3.00 £4.00 £5.00 £10.00

How can I put a formula in the pound sign that if the following staff at the
end of the week have the following

SPH- 0.37 AND 100% ATTENDANCE THE POUND SIGN SHOULD GET THE SUM FROM THE
SECOND TABLE SO IT WILL GET THE SALES & TIMES IT BY WHAT GROUP THEY ARE IN
THE SECOND TABLE.

So that person has got 10 sales, so that £4.00 a sales = 10*4= 40.00

 

[Bob Phillips]

As I said before

Re-cut the data like so, to cells M1:R8

Attendance 0.24 0.35 0.49 0.69 1
20%
39% 0.30 0.40
59% 0.40 0.50
79% 0.30 0.40 0.60 0.75
89% 0.50 0.75 1.00 2.00
99% 1.50 2.00 2.50 5.00
100% 3.00 4.00 5.00 10.00


and then in F1 use a formula of

=INDEX(N2:R8,MATCH(G1/100,M2:M8,1),MATCH(D1,N1:R1,1))*E1


--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Guest]

=INDEX(N2:R8,MATCH(G1/100,M2:M8,1),MATCH(D1,N1:R1,1))*E1

can you please let me what columns are the following,

GI/100= IS THATS THE TOTAL ATTENDANCE
*E1= IS THIS THE TOTAL SALES IS THAT RIGTH

THATS

 

[Bob Phillips]

That is correct

G1 is attendance (I divide by 100 as you show it as 100 not 100%, if it is a
percentage don't divide by 100)
D1 is the SPH
E1 is the sales figure

--

HTH

RP
(remove nothere from the email address if mailing direct)