Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Adding a second formatting condition. | Excel Worksheet Functions | |||
If formula---adding another condition | Excel Discussion (Misc queries) | |||
Adding a column of numbers, with a condition | New Users to Excel | |||
Adding cells based on condition | Excel Worksheet Functions | |||
Adding different validations based on condition | Excel Worksheet Functions |