Beginner in excel

  • Thread starter Thread starter Guest
  • Start date Start date
G

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.
 
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
 
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
 
Thanks for the help, made it very easy!
--
Regards,
Sarah


Sandy Mann said:
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
Click to expand...
 
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


Sarah said:
Thanks for the help, made it very easy!
Click to expand...
 
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
 
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")*D2:D17)

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
 
Back
Top