| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Subtotal help!! ASAP!!!!!!!!! Report due by noon!!!
I am not certain that I can correctly explain what I am looking for so if
this does not make sense please let me know!! :o)) Ok I have 3 columns Account number Account Name Linked account 123456 XYZ Customer 123456 123456 XYZ Customer 654321 The 1st column are my sold to account numbers (billing) and the linked column are my ship to account numbers(shipping locations). What I need to do is get the total linked account numbers, BUT I do not want to count the account number if it is the same as what is in column 1. I have done a subtotal to count at each change in account numbers in column 1 and 3. Does this make sense?!? |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Subtotal help!! ASAP!!!!!!!!! Report due by noon!!!
something like this?
=sumproduct((a2:a22=123456)*(c2:c22<123456)) -- Don Guillett Microsoft MVP Excel SalesAid Software "DestinySky" wrote in message ... I am not certain that I can correctly explain what I am looking for so if this does not make sense please let me know!! :o)) Ok I have 3 columns Account number Account Name Linked account 123456 XYZ Customer 123456 123456 XYZ Customer 654321 The 1st column are my sold to account numbers (billing) and the linked column are my ship to account numbers(shipping locations). What I need to do is get the total linked account numbers, BUT I do not want to count the account number if it is the same as what is in column 1. I have done a subtotal to count at each change in account numbers in column 1 and 3. Does this make sense?!? |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Subtotal help!! ASAP!!!!!!!!! Report due by noon!!!
On Fri, 2 Jan 2009 05:32:03 -0800, DestinySky
wrote: I am not certain that I can correctly explain what I am looking for so if this does not make sense please let me know!! :o)) Ok I have 3 columns Account number Account Name Linked account 123456 XYZ Customer 123456 123456 XYZ Customer 654321 The 1st column are my sold to account numbers (billing) and the linked column are my ship to account numbers(shipping locations). What I need to do is get the total linked account numbers, BUT I do not want to count the account number if it is the same as what is in column 1. I have done a subtotal to count at each change in account numbers in column 1 and 3. Does this make sense?!? What is "the total linked account numbers"?? Do you mean "the NUMBER of linked accounts"? What is the expected output in the given example? Is it 1 (one)? Please give more information. Maybe a more extensive example with expected output. Lars-Åke |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Subtotal help!! ASAP!!!!!!!!! Report due by noon!!!
A B C D E
Account Name Account linked 3153879 SH 0 ABC 3153879 3153879 SH 1 ABC 3156489 1 3153884 SH 0 XYZ 3153884 3153884 SH 1 XYZ 3156479 3153884 SH 2 XYZ 3156509 3153884 SH 3 XYZ 3156617 3153884 SH 4 XYZ 3156777 3153884 SH 5 XYZ 3155245 3153884 SH 6 XYZ 3159381 6 OK try this: I need to count how many rows are higher than Zero in column C. "Lars-Ã…ke Aspelin" wrote: On Fri, 2 Jan 2009 05:32:03 -0800, DestinySky wrote: I am not certain that I can correctly explain what I am looking for so if this does not make sense please let me know!! :o)) Ok I have 3 columns Account number Account Name Linked account 123456 XYZ Customer 123456 123456 XYZ Customer 654321 The 1st column are my sold to account numbers (billing) and the linked column are my ship to account numbers(shipping locations). What I need to do is get the total linked account numbers, BUT I do not want to count the account number if it is the same as what is in column 1. I have done a subtotal to count at each change in account numbers in column 1 and 3. Does this make sense?!? What is "the total linked account numbers"?? Do you mean "the NUMBER of linked accounts"? What is the expected output in the given example? Is it 1 (one)? Please give more information. Maybe a more extensive example with expected output. Lars-Ã…ke |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Subtotal help!! ASAP!!!!!!!!! Report due by noon!!!
I think this is what you want, although you should get rid of the
Subtotals first: =COUNTA($C$1:$C$10)-SUMPRODUCT(--($A$1:$A$10=$C$1:$C$10)) Not sure if you want Count or CountA, depends on your data. |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Subtotal help!! ASAP!!!!!!!!! Report due by noon!!!
On Jan 2, 9:19*am, Spiky wrote:
I think this is what you want, although you should get rid of the Subtotals first: =COUNTA($C$1:$C$10)-SUMPRODUCT(--($A$1:$A$10=$C$1:$C$10)) Not sure if you want Count or CountA, depends on your data. Should have mentioned....this formula applies to your original layout with 3 columns, not the one with 5 columns. |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Subtotal help!! ASAP!!!!!!!!! Report due by noon!!!
Hi,
If you can put the results in a new column, the following formula will do the trick =IF(A2,"",SUMPRODUCT(--(A$2:A2<C$2:C2))-SUM(D$1:D1)) This formula assumes that column A contains the Acct# and C contains the Linked#. It also assumes that column A is empty on the rows between each group, you could use column B for this purpose just as well (change A2 to B2). This also assumes you have data starting on row 2. Just copy the formula down column D. -- If this helps, please click the Yes button Cheers, Shane Devenshire "DestinySky" wrote: I am not certain that I can correctly explain what I am looking for so if this does not make sense please let me know!! :o)) Ok I have 3 columns Account number Account Name Linked account 123456 XYZ Customer 123456 123456 XYZ Customer 654321 The 1st column are my sold to account numbers (billing) and the linked column are my ship to account numbers(shipping locations). What I need to do is get the total linked account numbers, BUT I do not want to count the account number if it is the same as what is in column 1. I have done a subtotal to count at each change in account numbers in column 1 and 3. Does this make sense?!? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| SUBTOTAL ERRORS - PLEASE HELP ASAP | Excel Discussion (Misc queries) | |||
| pasting to subtotal lines without replacing hidden -non-subtotal l | Excel Discussion (Misc queries) | |||
| not able to create subtotal summary report | Excel Worksheet Functions | |||
| space after subtotal in report | Excel Discussion (Misc queries) | |||
| move to another cell within a subtotal report within a macro | Excel Worksheet Functions | |||
Linear Mode
