FORMULA HELP

[Megan]

My formula isn't working and having tried to solve it for the past hour -
I'll not throw it out to you clever lot.

I have two worksheets. one is of cash recvd and is laid out like so
A B C D
DATE REF VALUE METHOD


the other worksheet is Sales rep info Laid out like so
B C D E
CONTRACT REF VAL TOTAL


What I am trying to do is work out the total collected per ref no: The
formula I have tried is =if(cashrecvd!$B2:B3000,TA!C2,$C2:$C3000)

Please can you help me is driving me insane?

many thanks in anticpation of your support

 

[Sandy Mann]

Speaking personally I don't quite understand what it is that you are trying
to do - I'm not one of the clever lot <g>

Can you explain what it is that you want to do?

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[Max]

One guess

In your "Sales rep info" sheet,
you could place this in say, E2:
=SUMIF(cashrecvd!$B$2:$B$3000,C2,cashrecvd!$C$2:$C$3000)
Copy down

 

[Ron Rosenfeld]

My formula isn't working and having tried to solve it for the past hour -
I'll not throw it out to you clever lot.

I have two worksheets. one is of cash recvd and is laid out like so
A B C D
DATE REF VALUE METHOD


the other worksheet is Sales rep info Laid out like so
B C D E
CONTRACT REF VAL TOTAL


What I am trying to do is work out the total collected per ref no: The
formula I have tried is =if(cashrecvd!$B2:B3000,TA!C2,$C2:$C3000)

Please can you help me is driving me insane?

many thanks in anticpation of your support

Your formula is an array-formula. Are you "committing it" with
<ctrl><shift><enter> and is Excel placing the braces {...} around it?

Since you seem to have REF on both sheets, perhaps you could just use one sheet
and a SUMIF formula.
--ron

 

[Megan]

I need to know calculate the total amount of cash collected per reference no
for each sales rep and input it into a different sheet.

The cash sheet might have the following info.

Ref CASH DATE

MUC01 £24.99 25/11/07

The cash sheet has many payments on it like this. I need to calculate the
total collected for that ref no - but in another sheet

The sales sheet may look like this:

Ref Total

Muc01

So in the total column I need it to refer to the cash sheet and tell me the
total of payments collected for that ref no.

i hope this is clear - I am not very good at explaining myself.
--
Kind regards
Megan

Speaking personally I don't quite understand what it is that you are trying
to do - I'm not one of the clever lot <g>

Can you explain what it is that you want to do?

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk