Forumla question - I'm stumped

  • Thread starter Thread starter scdgoofy
  • Start date Start date
S

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
 
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
 
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
 
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
 
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.
 
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
 
Back
Top