SUMIF to subtotal accounts?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi there! THis may seem like a basic question to some of you, but I can't seem to get it to work! I am trying to sub-total various fees for various sub-accounts under one main account. I don't know how to specify the criteria properly. For example....How do I get Excel to generate the list the "Sub-Account Number" and "Sub-Acct Total" column I put in below based on the info provided on the left (Sub-Acct # and Fee)??!? ANy help would be appreciated!! THANKS!

Acct: 1234
Sub Acct # Fee Sub-Account Number Sub-Acct Tota
123 5.00 123 5.0
124 2.25 124 10.5
124 3.25 125 2.0
124 5.00 126 3.9
125 2.00 127 5.3
126 1.2
126 1.2
126 1.5
127 5.3
 
One way.
Assuming data in A2:B10.

In E2 (E1 must not contain data present in A2:A10)
enter this array-formula

=INDEX($A$2:$A$10,MIN(IF(COUNTIF($E$1:E1,
$A$2:$A$10)=0,ROW($A$2:$A$10)-ROW($A$2)+1)))

The formula must be entered with <Shift><Ctrl><Enter>,
also if edited later. Notice the use of mixed absolute ($$)
and relative (no cash) references.

In F2 enter:

=SUMIF($A$2:$A$10,E2,$B$2:$B$10)

Select E2:F2 and copy down with the fill handle
(the little square in the lower right corner of the
selection) until the value in A2 starts repeating
in column E.

--
Best Regards
Leo Heuser

Followup to newsgroup only please.

IngyBB said:
Hi there! THis may seem like a basic question to some of you, but I can't
Click to expand...
seem to get it to work! I am trying to sub-total various fees for various
sub-accounts under one main account. I don't know how to specify the
criteria properly. For example....How do I get Excel to generate the list
the "Sub-Account Number" and "Sub-Acct Total" column I put in below based on
the info provided on the left (Sub-Acct # and Fee)??!? ANy help would be
appreciated!! THANKS!!
 
=sumif(sub_acc_range,123,fee)

--

Regards,

Peo Sjoblom


IngyBB said:
Hi there! THis may seem like a basic question to some of you, but I can't
Click to expand...
seem to get it to work! I am trying to sub-total various fees for various
sub-accounts under one main account. I don't know how to specify the
criteria properly. For example....How do I get Excel to generate the list
the "Sub-Account Number" and "Sub-Acct Total" column I put in below based on
the info provided on the left (Sub-Acct # and Fee)??!? ANy help would be
appreciated!! THANKS!!
 
Now that's a question !

One way:

In cell D3 (say) put the formula: =SUMIF(A:A,C3,B:B) and drag down on
the autofill handle

assuming the sub accounts are in column A, the values are in column B and
your table is in column C starting in cell C3

Regards

Trevor


IngyBB said:
Hi there! THis may seem like a basic question to some of you, but I can't
Click to expand...
seem to get it to work! I am trying to sub-total various fees for various
sub-accounts under one main account. I don't know how to specify the
criteria properly. For example....How do I get Excel to generate the list
the "Sub-Account Number" and "Sub-Acct Total" column I put in below based on
the info provided on the left (Sub-Acct # and Fee)??!? ANy help would be
appreciated!! THANKS!!
 
Back
Top