Beginner in excel
 

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.
--
Thank you,
Sarah

Beginner in excel
 

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

"Sarah" wrote:

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.
--
Thank you,
Sarah

Beginner in excel
 

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


with @tiscali.co.uk


"Sarah" wrote in message
...
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.
--
Thank you,
Sarah

Beginner in excel
 

Thank you for the help! Much appreciated.
--
Regards,
Sarah


"FloMM2" wrote:

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

"Sarah" wrote:

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.
--
Thank you,
Sarah

Beginner in excel
 

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


"Sandy Mann" wrote:

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


with @tiscali.co.uk


"Sarah" wrote in message
...
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.
--
Thank you,
Sarah

Beginner in excel
 

You are very welcome, thanks for the feedback

--
Regards,

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


with @tiscali.co.uk


"Sarah" wrote in message
...
Thanks for the help, made it very easy!
--
Regards,
Sarah


"Sandy Mann" wrote:

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


with @tiscali.co.uk


"Sarah" wrote in message
...
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.
--
Thank you,
Sarah

Beginner in excel
 

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" wrote:

You are very welcome, thanks for the feedback

--
Regards,

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


with @tiscali.co.uk


"Sarah" wrote in message
...
Thanks for the help, made it very easy!
--
Regards,
Sarah


"Sandy Mann" wrote:

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


with @tiscali.co.uk


"Sarah" wrote in message
...
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.
--
Thank you,
Sarah

Beginner in excel
 

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


with @tiscali.co.uk


"Abhay" wrote in message
...
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" wrote:

You are very welcome, thanks for the feedback

--
Regards,

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


with @tiscali.co.uk


"Sarah" wrote in message
...
Thanks for the help, made it very easy!
--
Regards,
Sarah


"Sandy Mann" wrote:

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


with @tiscali.co.uk


"Sarah" wrote in message
...
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.
--
Thank you,
Sarah