sum certain cells in a column based on criteria from another colum

[Guest]

I have a huge spread sheet that I'd like to automate. My current delima is
how to calculate a dollar amount in one cell with information from column J
based on the criteria, same row, but in column N. I've tried the sumproduct
information but I'm entering it in wrong ... I've read the questions here
and I know this should be a relatively simply formula but I can not get my
brain wrapped around it. Any help would be appreciated.

Thanks

Donna

 

[T. Valko]

If you want a sum based on one condition then SUMIF is the function to use:

If column J = "X" sum column N:

=SUMIF(J:J,"X",N:N)

You would only need to use SUMPRODUCT if there is more than a single
condition involved:

If column J = "X" and column K = 100 sum column N:

=SUMPRODUCT(--(J1:J100="X"),--(K1:K100=100),N1:N100)

Note that with SUMPRODUCT you can't use entire columns as range references
(unless you're using Excel 2007).

Biff

 

[T. Valko]

If you want a sum based on one condition then SUMIF is the function to use

Under *most* circumstances.

There are some situations where a single condition will require SUMPRODUCT:

1234.....10
201.......20
1...........33
666.......17
55.........20

Sum column B where column A starts with a 1:

=SUMPRODUCT(--(LEFT(A1:A5)="1"),B1:B5)

Biff

 

[Guest]

I thought of the sumif function but here is what I can not get my brain
wrapped around

Column J = dollar amounts, of all shapes and sizes
Column N = a identifier of what kind of account that money is in, A, B, C,
or D for the different types of accounts.

What I'm wanting to do is in another cell elsewhere in the spreadsheet, pull
all the A's from column N and add the dollar amounts together for a sum in
that cell,
Below it another cell for the B's sum and C's combined,
and then another for D's

I could do a sort manually, that's what they have been doing before, but I'd
like to try to figure out a formula that will search out those identifiers
and sum up their corresponding $ amounts.

clear as mud?

 

[T. Valko]

SUMIF will do what you want:

A1 = A
A2 = B
A3 = C
A4 = D

Entered in B1 and copied down to B4:

=SUMIF(N:N,A1,J:J)

Biff

 

[Guest]

nope, that isn't working. ... the A, B, C or D's in Column N vary
depending on what kind of account retention. What I'm trying to do is
calculate a running total of all the C $amounts from column J in J3, the B
amounts in J2 and the D amounts in cell J6. Does that help?

Column J Column N

$7458965.15 D
$42215.09 A
$1897.98 D
$29426.12 Blank
$87452.96 B
$19872.32 C
etc.

 

[T. Valko]

Hmmm....

Based on your sample, if you enter this formula:

=SUMIF(N:N,"C",J:J)

The result is 19872.32

If that's not the result you want then I don't understand what you want.

Biff

 

[Guest]

OMG! thank you Biff!!

Hmmm....

Based on your sample, if you enter this formula:

=SUMIF(N:N,"C",J:J)

The result is 19872.32

If that's not the result you want then I don't understand what you want.

Biff

 

[T. Valko]

I hope that means you got this to work! Thanks for the feedback!

Biff