Subtotal help!! ASAP!!!!!!!!! Report due by noon!!!

[DestinySky]

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!! ))

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?!?

 

[Don Guillett]

something like this?
=sumproduct((a2:a22=123456)*(c2:c22<>123456))

 

[Lars-Åke Aspelin]

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!! ))

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

 

[DestinySky]

ok so what if my sheet looks like this?

Customer Name 1 Customer
3168472 Amazon.com Inc 3168472
3168472 Amazon.com Inc 7843010
5240378 Drugstore.com 5240378
5240378 Drugstore.com 5240386
5240386 Drugstore.com Inc 5240386
7843010 Amazon com kydc Inc 7843010

 

[DestinySky]

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.

 

[Spiky]

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.

 

[Spiky]

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.

 

[Shane Devenshire]

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$11))

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.