Adding up Cells Given a Condition
 

Hello.

I am wondering if anyone can help me.

I have cells A1:D4. Column A has check #. Column B has allocation (meaning
if I received a check and our client wants it split, how much goes to this
invoice and how much goes to another invoice). Column C shows Total Check
amount. Column D shows invoice # to apply.

Row 4 is the total line.

Example, I received 2 checks today. Check A for $50 all for one invoice (1)
and Check B for $80 to be applied to invoice 2 and 3 equally.

A B C D
1 A $50 $50 1
2 B $40 $80 2
3 B $40 $ - 3
4Total $90 $90

If a single check needs to be allocated to more than one invoice, I have to
show each of the allocation on a separate line item (see rows 2 and 3). But
in column C, I need to show how much, per actual check, is the amount (see
how cell C2 added B2 and B3 since these have = check #s in A2 and A3) but see
how C3 just shows -0- because B2 already totalled the check amount.

How I want Column C to behave is to add value adjacent to it if the check #
is unique. But if there are multiple instances of that check #, then add the
values in column B that has that check # but I only want this to calculate at
the cell where the first instance of the check # was found and not in
subsequent cells where it finds the same check # otherwise my grand total
will be all skewed.

Is this possible or am I dreaming?

Thank you.
Storm

Adding up Cells Given a Condition
 

Fortunately you are not dreaming...

Assuming headers in Row 1 enter this in C2
=IF(COUNTIF($B$2:B2,B2)=1,SUMIF($B$2:$B$1000,B2,$C $2:$C$1000),"")
adjust 100 to the last row containing your data
and copy down

"Storm" wrote:

Hello.

I am wondering if anyone can help me.

I have cells A1:D4. Column A has check #. Column B has allocation (meaning
if I received a check and our client wants it split, how much goes to this
invoice and how much goes to another invoice). Column C shows Total Check
amount. Column D shows invoice # to apply.

Row 4 is the total line.

Example, I received 2 checks today. Check A for $50 all for one invoice (1)
and Check B for $80 to be applied to invoice 2 and 3 equally.

A B C D
1 A $50 $50 1
2 B $40 $80 2
3 B $40 $ - 3
4Total $90 $90

If a single check needs to be allocated to more than one invoice, I have to
show each of the allocation on a separate line item (see rows 2 and 3). But
in column C, I need to show how much, per actual check, is the amount (see
how cell C2 added B2 and B3 since these have = check #s in A2 and A3) but see
how C3 just shows -0- because B2 already totalled the check amount.

How I want Column C to behave is to add value adjacent to it if the check #
is unique. But if there are multiple instances of that check #, then add the
values in column B that has that check # but I only want this to calculate at
the cell where the first instance of the check # was found and not in
subsequent cells where it finds the same check # otherwise my grand total
will be all skewed.

Is this possible or am I dreaming?

Thank you.
Storm

Adding up Cells Given a Condition
 

Hi Sheeloo,
Thank you for your response.

2 questions:
1) I am coming up with a circular reference error since I am putting this
in column C and the sum range formula includes C {$C$2:$C$1000}.
2) I'm a little confused as to why the range and criteria is pointing to
column B.

Thank you so much again.


"Sheeloo" wrote:

Fortunately you are not dreaming...

Assuming headers in Row 1 enter this in C2
=IF(COUNTIF($B$2:B2,B2)=1,SUMIF($B$2:$B$1000,B2,$C $2:$C$1000),"")
adjust 100 to the last row containing your data
and copy down

"Storm" wrote:

Hello.

I am wondering if anyone can help me.

I have cells A1:D4. Column A has check #. Column B has allocation (meaning
if I received a check and our client wants it split, how much goes to this
invoice and how much goes to another invoice). Column C shows Total Check
amount. Column D shows invoice # to apply.

Row 4 is the total line.

Example, I received 2 checks today. Check A for $50 all for one invoice (1)
and Check B for $80 to be applied to invoice 2 and 3 equally.

A B C D
1 A $50 $50 1
2 B $40 $80 2
3 B $40 $ - 3
4Total $90 $90

If a single check needs to be allocated to more than one invoice, I have to
show each of the allocation on a separate line item (see rows 2 and 3). But
in column C, I need to show how much, per actual check, is the amount (see
how cell C2 added B2 and B3 since these have = check #s in A2 and A3) but see
how C3 just shows -0- because B2 already totalled the check amount.

How I want Column C to behave is to add value adjacent to it if the check #
is unique. But if there are multiple instances of that check #, then add the
values in column B that has that check # but I only want this to calculate at
the cell where the first instance of the check # was found and not in
subsequent cells where it finds the same check # otherwise my grand total
will be all skewed.

Is this possible or am I dreaming?

Thank you.
Storm

Adding up Cells Given a Condition
 

Sorry I had running serial number in Col A...

Use this in C2 and copy down
=IF(COUNTIF($A$2:A2,A2)=1,SUMIF($A$2:$A$1000,A2,$B $2:$B$1000),"")

"Storm" wrote:

Hi Sheeloo,
Thank you for your response.

2 questions:
1) I am coming up with a circular reference error since I am putting this
in column C and the sum range formula includes C {$C$2:$C$1000}.
2) I'm a little confused as to why the range and criteria is pointing to
column B.

Thank you so much again.


"Sheeloo" wrote:

Fortunately you are not dreaming...

Assuming headers in Row 1 enter this in C2
=IF(COUNTIF($B$2:B2,B2)=1,SUMIF($B$2:$B$1000,B2,$C $2:$C$1000),"")
adjust 100 to the last row containing your data
and copy down

"Storm" wrote:

Hello.

I am wondering if anyone can help me.

I have cells A1:D4. Column A has check #. Column B has allocation (meaning
if I received a check and our client wants it split, how much goes to this
invoice and how much goes to another invoice). Column C shows Total Check
amount. Column D shows invoice # to apply.

Row 4 is the total line.

Example, I received 2 checks today. Check A for $50 all for one invoice (1)
and Check B for $80 to be applied to invoice 2 and 3 equally.

A B C D
1 A $50 $50 1
2 B $40 $80 2
3 B $40 $ - 3
4Total $90 $90

If a single check needs to be allocated to more than one invoice, I have to
show each of the allocation on a separate line item (see rows 2 and 3). But
in column C, I need to show how much, per actual check, is the amount (see
how cell C2 added B2 and B3 since these have = check #s in A2 and A3) but see
how C3 just shows -0- because B2 already totalled the check amount.

How I want Column C to behave is to add value adjacent to it if the check #
is unique. But if there are multiple instances of that check #, then add the
values in column B that has that check # but I only want this to calculate at
the cell where the first instance of the check # was found and not in
subsequent cells where it finds the same check # otherwise my grand total
will be all skewed.

Is this possible or am I dreaming?

Thank you.
Storm

Adding up Cells Given a Condition
 

Thank you Sheeloo!

"Sheeloo" wrote:

Sorry I had running serial number in Col A...

Use this in C2 and copy down
=IF(COUNTIF($A$2:A2,A2)=1,SUMIF($A$2:$A$1000,A2,$B $2:$B$1000),"")

"Storm" wrote:

Hi Sheeloo,
Thank you for your response.

2 questions:
1) I am coming up with a circular reference error since I am putting this
in column C and the sum range formula includes C {$C$2:$C$1000}.
2) I'm a little confused as to why the range and criteria is pointing to
column B.

Thank you so much again.


"Sheeloo" wrote:

Fortunately you are not dreaming...

Assuming headers in Row 1 enter this in C2
=IF(COUNTIF($B$2:B2,B2)=1,SUMIF($B$2:$B$1000,B2,$C $2:$C$1000),"")
adjust 100 to the last row containing your data
and copy down

"Storm" wrote:

Hello.

I am wondering if anyone can help me.

I have cells A1:D4. Column A has check #. Column B has allocation (meaning
if I received a check and our client wants it split, how much goes to this
invoice and how much goes to another invoice). Column C shows Total Check
amount. Column D shows invoice # to apply.

Row 4 is the total line.

Example, I received 2 checks today. Check A for $50 all for one invoice (1)
and Check B for $80 to be applied to invoice 2 and 3 equally.

A B C D
1 A $50 $50 1
2 B $40 $80 2
3 B $40 $ - 3
4Total $90 $90

If a single check needs to be allocated to more than one invoice, I have to
show each of the allocation on a separate line item (see rows 2 and 3). But
in column C, I need to show how much, per actual check, is the amount (see
how cell C2 added B2 and B3 since these have = check #s in A2 and A3) but see
how C3 just shows -0- because B2 already totalled the check amount.

How I want Column C to behave is to add value adjacent to it if the check #
is unique. But if there are multiple instances of that check #, then add the
values in column B that has that check # but I only want this to calculate at
the cell where the first instance of the check # was found and not in
subsequent cells where it finds the same check # otherwise my grand total
will be all skewed.

Is this possible or am I dreaming?

Thank you.
Storm