Beginner in excel

[Guest]

Hi,
I have set up a stocklist worksheet, and have column J entitled PAYMENT
METHOD, in cell J4, I have put number 1 (to represent CASH PAYMENT), number
2(to represent cheque payment, with the amount paid in column K, and the
description of the item in column B. I want to enter a formula that calcutes
the CASH amounts from column K, when column J says 1, and the same for cheque
amounts when column J says 2??? Have been playing, but just can't figure out
how to achieve this.

 

[Guest]

Sarah,
This is one possible solution:
Column J has a "1 =cash or 2=check"
Column K has the amount.
Column L has this formula"=IF($J$2=1,$K$2,0)"
This formula is looking at the J column for 1, if it is a 1, then bring the
amount in K2.
Column M has this formula"=IF($J$2=2,$K$2,0)"
This formula is looking at J column for 2, if it is a 2, then bring the
amount in K2.
I put the "$" in so you can copydown, without the formula changing on you.
You will have to add a totall at the bottom of your spreadsheet to total
"Cash" and "Check" amounts.
HTH

 

[Sandy Mann]

For Cash use:

=SUMIF(J3:J10,1,K3:K10)

For Cheques use:

=SUMIF(J3:J10,2,K3:K10)

Adjust the ranges to suit your spreadsheet.

--
HTH

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

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

 

[Guest]

Thank you for the help! Much appreciated.

 

[Guest]

Thanks for the help, made it very easy!
--
Regards,
Sarah

For Cash use:

=SUMIF(J3:J10,1,K3:K10)

For Cheques use:

=SUMIF(J3:J10,2,K3:K10)

Adjust the ranges to suit your spreadsheet.

--
HTH

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

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

 

[Sandy Mann]

You are very welcome, thanks for the feedback

--
Regards,

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

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

Thanks for the help, made it very easy!

 

[Guest]

Is it possible to have multiple criteria in SUMIF formula?
For example, In Sarah's example, sum all checks if drawn on specific bank.
Bank name will be a separate column.
I have similar problem where I am trying to add quantity of stocks if it
matches the Company name and if it is a Buy transactions

Any help is well appreciated

Regards
Abhay

 

[Sandy Mann]

For multiple criteria use SUMPRODUCT()

With Company Name in Column A, "Buy" in Column C and Quantity of Stocks in D
use:

=SUMPRODUCT((A2:A17="Acme")*(C2:C17="Buy")*D217)

Adjust Name and ranges to suit your worksheet.
--
HTH

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

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