Forumla question - I'm stumped

[scdgoofy]

Column B is a list of states ("CA", "MI", "KY").
Column Q is a list of dollar amounts.

I want a single cell at the bottom that calculates the sum of dolla
amounts in column Q *IF* column B in the corresponding row reads "CA".

Sorting column B by state and then adding a "CA" range won't wor
because I'll need to re-sort the data many different ways.

Make sense?

Thanks so much in advance for your help

 

[Frank Kabel]

Hi
try
=SUMIF(B1:B1000,"CA",Q1:Q1000)

if you want to use a cell reference for your condition (e.g. A1 stores
'CA') use
=SUMIF(B1:B1000,"=" & A1,Q1:Q1000)

Frank

 

[scdgoofy]

Got it:

=SUMIF(B9:B245,"CA",Q9:Q245)



Now how would I count the number of "CA" occurances in column B whos
column Q value is greater than $0.00

 

[Frank Kabel]

Hi
try
=SUMPRODUCT((B9:B245="CA")*(Q9:Q245>0))
as you're are testing for two conditions neither COUNTIF nor SUMIF will
work (they only accept one condition)

Frank

 

[Bonnie]

at the bottom of your data
you can do a sumif formula:
You can change the state everytime you want see a diff st.

do this:

A column (whatever is the last empty cell/row)lets say it
is

A200 (put in there the state CA)
B200 (put in the formula.. =SUMIF(b:b,a200,q:q)
everytime you want to see a different state just change
cell A200 to that state.

Also, if you want to get a recap by state just do a pivot
table (this will recap by every state and add all the
totals with a subtotal.)

hope that helps.

 

[Rebecca]

Hi,

I was just looking into a problem similar to this and
found the answer. You could use the SUMIF function.

Your would place the function in the cell where you want
the sum to appear.

Here's an example:

=SUMIF(B1:B50,"CA",Q1:Q50)

You would change cell range to go to the end of your list
(In by example I had it look at rows 1 through 50)

You could also change the CA to MI or whatever state you
want to sum.

Hope that helps!
Rebecca