SumIf will not work in this case

  • Thread starter Thread starter Need help with sumif
  • Start date Start date
N

Need help with sumif

How Do I use SUMIF

A:A = "Apple" and B:B >= 50% then sum C:C


Let me know if there is other solution other then SUMIF also
 
SUMIF is a wrong choice. If you use XL-2007 then use SUMIFS(), prior to
XL-2007 use SUMPRODUCT()

=SUMPRODUCT(--(A1:A100="Apple"),--(B1:B100>=0.5),C1:C100)

You can't not use a whole column unless you use XL-2007
 
If you have more than one criteria then you can't use SUMIF - use
SUMPRODUCT instead. If you are using XL2003 or earlier then you can't
use full-column references with SP, and it is better to keep the
ranges just as large as required to cover your data. Something like
this:

=SUMPRODUCT(--(A1:A100="Apple"),--(B1:B100>=50%),C1:C100)

The -- converts True and False to 1 and 0 respectively for each
element. Another way of writing it is:

=SUMPRODUCT((A1:A100="Apple")*(B1:B100>=50%),C1:C100)

or even:

=SUMPRODUCT((A1:A100="Apple")*(B1:B100>=50%)*(C1:C100))

where * is equivalent to AND.

Hope this helps.

Pete
 
With two or more criteria, always think SUMPRODUCT():

=SUMPRODUCT((A1:A100="Apple")*(B1:B100>=50%)*(C1:C100))
 
Well

let me rephrase my question maybe I explained wrong

I'm trying to find only "Apple" in column A also find only it's sale is
more than 50% in column B then sum C column that's in a same row
 
Thank you it works perfect

Need help with sumif said:
Well

let me rephrase my question maybe I explained wrong

I'm trying to find only "Apple" in column A also find only it's sale is
more than 50% in column B then sum C column that's in a same row
Click to expand...
 
Thank you very much

Gary''s Student said:
With two or more criteria, always think SUMPRODUCT():

=SUMPRODUCT((A1:A100="Apple")*(B1:B100>=50%)*(C1:C100))
Click to expand...
 
Thank you for the details

Pete_UK said:
If you have more than one criteria then you can't use SUMIF - use
SUMPRODUCT instead. If you are using XL2003 or earlier then you can't
use full-column references with SP, and it is better to keep the
ranges just as large as required to cover your data. Something like
this:

=SUMPRODUCT(--(A1:A100="Apple"),--(B1:B100>=50%),C1:C100)

The -- converts True and False to 1 and 0 respectively for each
element. Another way of writing it is:

=SUMPRODUCT((A1:A100="Apple")*(B1:B100>=50%),C1:C100)

or even:

=SUMPRODUCT((A1:A100="Apple")*(B1:B100>=50%)*(C1:C100))

where * is equivalent to AND.

Hope this helps.

Pete
Click to expand...
 
Hi,

In 2007 you can use SUMIFS for this example it would be

=SUMIFS(C1:C100,A1:A100,"Apple",B1:B100,">=50%")
 
Back
Top