Using multiple conditions in formula like countif(), sumif()

Hi.
To some formulas like countif(); sumif(), we can type 1 condition to sort the
relevant data into use.
How about if I wish to use more than 1 condition?

Eg:
A------12
B------9
C------18
i-------30
ii-------45
iii------32

I would like to set the following conditions when using formulas like countif()
& sumif():
- select the right rows (eg containing A/B/C or i/ii/iii)
&
- select the right range of numbers (eg:
--- 10-20 [inclusive] for values in A/B/C;
--- 35-45[inclusive] for values in i/ii/iii)

How to do?


--
Additional information:
- I'm using Office XP
- I'm using Windows XP

>
> Hi.
> To some formulas like countif(); sumif(), we can type 1 condition to sort the
> relevant data into use.
> How about if I wish to use more than 1 condition?

Put it simple.
What I want is, eg:
sumif(range, criteria1, criteria 2 and so on, sum_cell)
So how to do the "criteria1, criteria 2 and so on" bits?

Forget about the example if it sounds complicated.

Good morning 0-0 Wai Wai ^-^

This is an answer I posted to a recent post asking a smiliar question. Does this help?



A B C
Product 1 Manchester 10
Product 1 London 15
Product 2 Manchester 12
Product 1 London 20
Product 1 London 25
Product 2 London 11
Product 1 Manchester 8
Product 2 Manchester 6
Product 1 Manchester 5
Product 1 Manchester 4

This formula will add up all the occasions where column A=product 1 and column B = Manchester.

=SUM(IF($A$2:$A$11="Product 1",IF$B$2:$B$11="Manchester",$C$2:$C$11,0),0))

Just remember it's an array formula so to commit it use ctrl + alt + enter.


HTH

DominicB

--

HTH

RP
(remove nothere from the email address if mailing direct)


"0-0 Wai Wai ^-^" <x@x.com> wrote in message
news:efJd7IhqFHA.2880@TK2MSFTNGP12.phx.gbl...
>
> Hi.
> To some formulas like countif(); sumif(), we can type 1 condition to sort
the
> relevant data into use.
> How about if I wish to use more than 1 condition?
>
> Eg:
> A------12
> B------9
> C------18
> i-------30
> ii-------45
> iii------32
>
> I would like to set the following conditions when using formulas like
countif()
> & sumif():
> - select the right rows (eg containing A/B/C or i/ii/iii)
> &
> - select the right range of numbers (eg:
> --- 10-20 [inclusive] for values in A/B/C;
> --- 35-45[inclusive] for values in i/ii/iii)
>
> How to do?
>
>
> --
> Additional information:
> - I'm using Office XP
> - I'm using Windows XP
>
>
>

An example of the first

=SUMPRODUCT((A1:A20={"A","B","C"})*(B1:B20>=10)*(B1:B20<=20)*(B1:B20))

--

HTH

RP
(remove nothere from the email address if mailing direct)


"0-0 Wai Wai ^-^" <x@x.com> wrote in message
news:efJd7IhqFHA.2880@TK2MSFTNGP12.phx.gbl...
>
> Hi.
> To some formulas like countif(); sumif(), we can type 1 condition to sort
the
> relevant data into use.
> How about if I wish to use more than 1 condition?
>
> Eg:
> A------12
> B------9
> C------18
> i-------30
> ii-------45
> iii------32
>
> I would like to set the following conditions when using formulas like
countif()
> & sumif():
> - select the right rows (eg containing A/B/C or i/ii/iii)
> &
> - select the right range of numbers (eg:
> --- 10-20 [inclusive] for values in A/B/C;
> --- 35-45[inclusive] for values in i/ii/iii)
>
> How to do?
>
>
> --
> Additional information:
> - I'm using Office XP
> - I'm using Windows XP
>
>
>

Add values in Column C where data in Column A matches criteria1 *OR*
criteria2:

=SUM(SUMIF(A1:A10,{"criteria1","criteria2"},C1:C10))


Add values in Column C where data in Column A matches criteria1 *AND*
Data in Column B matches criteria2:

=SUMPRODUCT((A1:A10="criteria1")*(B1:B10="criteria2")*C1:C10)

--

HTH,

RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================

"0-0 Wai Wai ^-^" <x@x.com> wrote in message
news:exn8kLhqFHA.2996@tk2msftngp13.phx.gbl...


>
> Hi.
> To some formulas like countif(); sumif(), we can type 1 condition to sort
the
> relevant data into use.
> How about if I wish to use more than 1 condition?

Put it simple.
What I want is, eg:
sumif(range, criteria1, criteria 2 and so on, sum_cell)
So how to do the "criteria1, criteria 2 and so on" bits?

Forget about the example if it sounds complicated.

Sorry but it seems neither of your formula works.
I think I must be mistaken in somewhere.
Here's my reply:

"RagDyeR" <ragdyer@cutoutmsn.com> 在郵件
news:edNqYzhqFHA.692@TK2MSFTNGP10.phx.gbl 中撰寫...
> Add values in Column C where data in Column A matches criteria1 *OR*
> criteria2:
>
> =SUM(SUMIF(A1:A10,{"criteria1","criteria2"},C1:C10))

What are {"criteria1","criteria2"}?
I try to type something like:
=SUMIF(N29:N34,{"free","cost"},O29:O34)
It works as if it only verify the first criteria only (ie the critieria "free").

Here's my example table:
Free -4 -4
Free 10 10
Free -5 -5
Cost -80 -80
Cost -90 -90
Cost 100 100


Expected answer = -69
Actual given answer = 1 (which is wrong).

>
> Add values in Column C where data in Column A matches criteria1 *AND*
> Data in Column B matches criteria2:
>
> =SUMPRODUCT((A1:A10="criteria1")*(B1:B10="criteria2")*C1:C10)


It gives 0 when I type something like:
=SUMPRODUCT((N29:N34="Free")*(O29:O34="10"))
=SUMPRODUCT((N29:N34="Free")*(O29:O34="10")*O29:O34)
=SUMPRODUCT((N29:N34="Free")*(O29:O34="10")*P29:P34)

Expected answer should be 10.
Try to replace (*) with (,) in vain.

Your first problem is that you didn't *copy* my formula!

Didn't I wrap the Sumif() function in a Sum() function?

Your second problem is that you're enclosing a number in quotes, therefore
converting it to text.

Remove the quotes from around the 10.
--

HTH,

RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================

"0-0 Wai Wai ^-^" <x@x.com> wrote in message
news:uH1usGiqFHA.528@TK2MSFTNGP09.phx.gbl...

Sorry but it seems neither of your formula works.
I think I must be mistaken in somewhere.
Here's my reply:

"RagDyeR" <ragdyer@cutoutmsn.com> 在郵件
news:edNqYzhqFHA.692@TK2MSFTNGP10.phx.gbl 中撰寫...
> Add values in Column C where data in Column A matches criteria1 *OR*
> criteria2:
>
> =SUM(SUMIF(A1:A10,{"criteria1","criteria2"},C1:C10))

What are {"criteria1","criteria2"}?
I try to type something like:
=SUMIF(N29:N34,{"free","cost"},O29:O34)
It works as if it only verify the first criteria only (ie the critieria
"free").

Here's my example table:
Free -4 -4
Free 10 10
Free -5 -5
Cost -80 -80
Cost -90 -90
Cost 100 100


Expected answer = -69
Actual given answer = 1 (which is wrong).

>
> Add values in Column C where data in Column A matches criteria1 *AND*
> Data in Column B matches criteria2:
>
> =SUMPRODUCT((A1:A10="criteria1")*(B1:B10="criteria2")*C1:C10)


It gives 0 when I type something like:
=SUMPRODUCT((N29:N34="Free")*(O29:O34="10"))
=SUMPRODUCT((N29:N34="Free")*(O29:O34="10")*O29:O34)
=SUMPRODUCT((N29:N34="Free")*(O29:O34="10")*P29:P34)

Expected answer should be 10.
Try to replace (*) with (,) in vain.

> Your first problem is that you didn't *copy* my formula!
>
> Didn't I wrap the Sumif() function in a Sum() function?
>
> Your second problem is that you're enclosing a number in quotes, therefore
> converting it to text.
>
> Remove the quotes from around the 10.

Thanks, all problems are solved like a charm.
By the way, sorry for my ignorance, I don't understand why these formulas could
work.

(1)
=SUM(SUMIF(A1:A10,{"criteria1","criteria2"},C1:C10))

As far as I know, SUMIF should perform the summing already.
Why do we need to add 1 more SUM to make it work?
Probably the tricky part is in {}.
What does {} mean? Array?

If so, it seems array will break the summing function in SUMIF, so it needs one
more SUM function.
I tihnk I am definitely going on a very wrong the track.


(2)
=SUMPRODUCT((A1:A10="criteria1")*(B1:B10="criteria2")*C1:C10)

What does the symbol (*) mean? What is it called?
What's its use?

When I look at Excel HELP, it doesn't explain that (*).
If, say, I use the commas (,) instead, it will falsify the statement.

i would start using a database instead of excel; it is about 100 times
more powerful

If you take the Sumif() formula that you originally tested (the one *not*
wrapped in Sum() ), where you only received the return of the "first" match,
And select the entire formula in the formula bar,
And then hit <F9> (that's Function Key F9)
You'll see that an array of values is displayed.

This shows that the Sumif() function *is* evaluating the *entire* array of
arguments, *BUT*, it's only set to return a single (first) evaluation.
Don't ask me why!?!?<g>

So, since the evaluations *are there*, the Sum() function adds them up and
returns the total.


As far as SumProduct(), check out this link to Bob Philips' web page on the
function, which will explain *much more* then what you asked about here.

http://www.xldynamic.com/source/xld.SUMPRODUCT.html

--

HTH,

RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================

"0-0 Wai Wai ^-^" <x@x.com> wrote in message
news:ut%23Zs1iqFHA.3352@TK2MSFTNGP14.phx.gbl...

> Your first problem is that you didn't *copy* my formula!
>
> Didn't I wrap the Sumif() function in a Sum() function?
>
> Your second problem is that you're enclosing a number in quotes, therefore
> converting it to text.
>
> Remove the quotes from around the 10.

Thanks, all problems are solved like a charm.
By the way, sorry for my ignorance, I don't understand why these formulas
could
work.

(1)
=SUM(SUMIF(A1:A10,{"criteria1","criteria2"},C1:C10))

As far as I know, SUMIF should perform the summing already.
Why do we need to add 1 more SUM to make it work?
Probably the tricky part is in {}.
What does {} mean? Array?

If so, it seems array will break the summing function in SUMIF, so it needs
one
more SUM function.
I tihnk I am definitely going on a very wrong the track.


(2)
=SUMPRODUCT((A1:A10="criteria1")*(B1:B10="criteria2")*C1:C10)

What does the symbol (*) mean? What is it called?
What's its use?

When I look at Excel HELP, it doesn't explain that (*).
If, say, I use the commas (,) instead, it will falsify the statement.

(can we if(and(a6>0,a10>0,a15>0),9.75,(" ')) want if any cell has entry then
9.75 else (" ") Or are we limited to two criteria. Don't see what I am
missing.

"RagDyeR" wrote:

> If you take the Sumif() formula that you originally tested (the one *not*
> wrapped in Sum() ), where you only received the return of the "first" match,
> And select the entire formula in the formula bar,
> And then hit <F9> (that's Function Key F9)
> You'll see that an array of values is displayed.
>
> This shows that the Sumif() function *is* evaluating the *entire* array of
> arguments, *BUT*, it's only set to return a single (first) evaluation.
> Don't ask me why!?!?<g>
>
> So, since the evaluations *are there*, the Sum() function adds them up and
> returns the total.
>
>
> As far as SumProduct(), check out this link to Bob Philips' web page on the
> function, which will explain *much more* then what you asked about here.
>
> http://www.xldynamic.com/source/xld.SUMPRODUCT.html
>
> --
>
> HTH,
>
> RD
> =====================================================
> Please keep all correspondence within the Group, so all may benefit!
> =====================================================
>
> "0-0 Wai Wai ^-^" <x@x.com> wrote in message
> news:ut%23Zs1iqFHA.3352@TK2MSFTNGP14.phx.gbl...
>
> > Your first problem is that you didn't *copy* my formula!
> >
> > Didn't I wrap the Sumif() function in a Sum() function?
> >
> > Your second problem is that you're enclosing a number in quotes, therefore
> > converting it to text.
> >
> > Remove the quotes from around the 10.
>
> Thanks, all problems are solved like a charm.
> By the way, sorry for my ignorance, I don't understand why these formulas
> could
> work.
>
> (1)
> =SUM(SUMIF(A1:A10,{"criteria1","criteria2"},C1:C10))
>
> As far as I know, SUMIF should perform the summing already.
> Why do we need to add 1 more SUM to make it work?
> Probably the tricky part is in {}.
> What does {} mean? Array?
>
> If so, it seems array will break the summing function in SUMIF, so it needs
> one
> more SUM function.
> I tihnk I am definitely going on a very wrong the track.
>
>
> (2)
> =SUMPRODUCT((A1:A10="criteria1")*(B1:B10="criteria2")*C1:C10)
>
> What does the symbol (*) mean? What is it called?
> What's its use?
>
> When I look at Excel HELP, it doesn't explain that (*).
> If, say, I use the commas (,) instead, it will falsify the statement.
>
>
>
>
>

=IF(AND(A6>0,A10>0,A15>0),9.75," ") is valid, but how does that relate to
the previous question?

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Curt" <Curt@discussions.microsoft.com> wrote in message
news:F7DA596D-5AB6-42C5-AD6D-6546103ACB02@microsoft.com...
> (can we if(and(a6>0,a10>0,a15>0),9.75,(" ')) want if any cell has entry
then
> 9.75 else (" ") Or are we limited to two criteria. Don't see what I am
> missing.
>
> "RagDyeR" wrote:
>
> > If you take the Sumif() formula that you originally tested (the one
*not*
> > wrapped in Sum() ), where you only received the return of the "first"
match,
> > And select the entire formula in the formula bar,
> > And then hit <F9> (that's Function Key F9)
> > You'll see that an array of values is displayed.
> >
> > This shows that the Sumif() function *is* evaluating the *entire* array
of
> > arguments, *BUT*, it's only set to return a single (first) evaluation.
> > Don't ask me why!?!?<g>
> >
> > So, since the evaluations *are there*, the Sum() function adds them up
and
> > returns the total.
> >
> >
> > As far as SumProduct(), check out this link to Bob Philips' web page on
the
> > function, which will explain *much more* then what you asked about here.
> >
> > http://www.xldynamic.com/source/xld.SUMPRODUCT.html
> >
> > --
> >
> > HTH,
> >
> > RD
> > =====================================================
> > Please keep all correspondence within the Group, so all may benefit!
> > =====================================================
> >
> > "0-0 Wai Wai ^-^" <x@x.com> wrote in message
> > news:ut%23Zs1iqFHA.3352@TK2MSFTNGP14.phx.gbl...
> >
> > > Your first problem is that you didn't *copy* my formula!
> > >
> > > Didn't I wrap the Sumif() function in a Sum() function?
> > >
> > > Your second problem is that you're enclosing a number in quotes,
therefore
> > > converting it to text.
> > >
> > > Remove the quotes from around the 10.
> >
> > Thanks, all problems are solved like a charm.
> > By the way, sorry for my ignorance, I don't understand why these
formulas
> > could
> > work.
> >
> > (1)
> > =SUM(SUMIF(A1:A10,{"criteria1","criteria2"},C1:C10))
> >
> > As far as I know, SUMIF should perform the summing already.
> > Why do we need to add 1 more SUM to make it work?
> > Probably the tricky part is in {}.
> > What does {} mean? Array?
> >
> > If so, it seems array will break the summing function in SUMIF, so it
needs
> > one
> > more SUM function.
> > I tihnk I am definitely going on a very wrong the track.
> >
> >
> > (2)
> > =SUMPRODUCT((A1:A10="criteria1")*(B1:B10="criteria2")*C1:C10)
> >
> > What does the symbol (*) mean? What is it called?
> > What's its use?
> >
> > When I look at Excel HELP, it doesn't explain that (*).
> > If, say, I use the commas (,) instead, it will falsify the statement.
> >
> >
> >
> >
> >

maybe
=IF(COUNT(M3,M5,M8)>0,1,2)

--
Don Guillett
SalesAid Software
donaldb@281.com
"Curt" <Curt@discussions.microsoft.com> wrote in message
news:F7DA596D-5AB6-42C5-AD6D-6546103ACB02@microsoft.com...
> (can we if(and(a6>0,a10>0,a15>0),9.75,(" ')) want if any cell has entry
then
> 9.75 else (" ") Or are we limited to two criteria. Don't see what I am
> missing.
>
> "RagDyeR" wrote:
>
> > If you take the Sumif() formula that you originally tested (the one
*not*
> > wrapped in Sum() ), where you only received the return of the "first"
match,
> > And select the entire formula in the formula bar,
> > And then hit <F9> (that's Function Key F9)
> > You'll see that an array of values is displayed.
> >
> > This shows that the Sumif() function *is* evaluating the *entire* array
of
> > arguments, *BUT*, it's only set to return a single (first) evaluation.
> > Don't ask me why!?!?<g>
> >
> > So, since the evaluations *are there*, the Sum() function adds them up
and
> > returns the total.
> >
> >
> > As far as SumProduct(), check out this link to Bob Philips' web page on
the
> > function, which will explain *much more* then what you asked about here.
> >
> > http://www.xldynamic.com/source/xld.SUMPRODUCT.html
> >
> > --
> >
> > HTH,
> >
> > RD
> > =====================================================
> > Please keep all correspondence within the Group, so all may benefit!
> > =====================================================
> >
> > "0-0 Wai Wai ^-^" <x@x.com> wrote in message
> > news:ut%23Zs1iqFHA.3352@TK2MSFTNGP14.phx.gbl...
> >
> > > Your first problem is that you didn't *copy* my formula!
> > >
> > > Didn't I wrap the Sumif() function in a Sum() function?
> > >
> > > Your second problem is that you're enclosing a number in quotes,
therefore
> > > converting it to text.
> > >
> > > Remove the quotes from around the 10.
> >
> > Thanks, all problems are solved like a charm.
> > By the way, sorry for my ignorance, I don't understand why these
formulas
> > could
> > work.
> >
> > (1)
> > =SUM(SUMIF(A1:A10,{"criteria1","criteria2"},C1:C10))
> >
> > As far as I know, SUMIF should perform the summing already.
> > Why do we need to add 1 more SUM to make it work?
> > Probably the tricky part is in {}.
> > What does {} mean? Array?
> >
> > If so, it seems array will break the summing function in SUMIF, so it
needs
> > one
> > more SUM function.
> > I tihnk I am definitely going on a very wrong the track.
> >
> >
> > (2)
> > =SUMPRODUCT((A1:A10="criteria1")*(B1:B10="criteria2")*C1:C10)
> >
> > What does the symbol (*) mean? What is it called?
> > What's its use?
> >
> > When I look at Excel HELP, it doesn't explain that (*).
> > If, say, I use the commas (,) instead, it will falsify the statement.
> >
> >
> >
> >
> >

In reading between the lines, you're using "AND" in your formula ... BUT ...
you're stating:
<<<"if *ANY* cell has entry">>>

So, try this:

=IF(OR(A6>0,A10>0,A15>0),9.75,"")

I also changed your last argument.
It's wiser to use a "null" ( "" ) instead of a space ( " " ).

If you really want or need a "space", put it back.
--
HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


"Curt" <Curt@discussions.microsoft.com> wrote in message
news:F7DA596D-5AB6-42C5-AD6D-6546103ACB02@microsoft.com...
> (can we if(and(a6>0,a10>0,a15>0),9.75,(" ')) want if any cell has entry
then
> 9.75 else (" ") Or are we limited to two criteria. Don't see what I am
> missing.
>
> "RagDyeR" wrote:
>
> > If you take the Sumif() formula that you originally tested (the one
*not*
> > wrapped in Sum() ), where you only received the return of the "first"
match,
> > And select the entire formula in the formula bar,
> > And then hit <F9> (that's Function Key F9)
> > You'll see that an array of values is displayed.
> >
> > This shows that the Sumif() function *is* evaluating the *entire* array
of
> > arguments, *BUT*, it's only set to return a single (first) evaluation.
> > Don't ask me why!?!?<g>
> >
> > So, since the evaluations *are there*, the Sum() function adds them up
and
> > returns the total.
> >
> >
> > As far as SumProduct(), check out this link to Bob Philips' web page on
the
> > function, which will explain *much more* then what you asked about here.
> >
> > http://www.xldynamic.com/source/xld.SUMPRODUCT.html
> >
> > --
> >
> > HTH,
> >
> > RD
> > =====================================================
> > Please keep all correspondence within the Group, so all may benefit!
> > =====================================================
> >
> > "0-0 Wai Wai ^-^" <x@x.com> wrote in message
> > news:ut%23Zs1iqFHA.3352@TK2MSFTNGP14.phx.gbl...
> >
> > > Your first problem is that you didn't *copy* my formula!
> > >
> > > Didn't I wrap the Sumif() function in a Sum() function?
> > >
> > > Your second problem is that you're enclosing a number in quotes,
therefore
> > > converting it to text.
> > >
> > > Remove the quotes from around the 10.
> >
> > Thanks, all problems are solved like a charm.
> > By the way, sorry for my ignorance, I don't understand why these
formulas
> > could
> > work.
> >
> > (1)
> > =SUM(SUMIF(A1:A10,{"criteria1","criteria2"},C1:C10))
> >
> > As far as I know, SUMIF should perform the summing already.
> > Why do we need to add 1 more SUM to make it work?
> > Probably the tricky part is in {}.
> > What does {} mean? Array?
> >
> > If so, it seems array will break the summing function in SUMIF, so it
needs
> > one
> > more SUM function.
> > I tihnk I am definitely going on a very wrong the track.
> >
> >
> > (2)
> > =SUMPRODUCT((A1:A10="criteria1")*(B1:B10="criteria2")*C1:C10)
> >
> > What does the symbol (*) mean? What is it called?
> > What's its use?
> >
> > When I look at Excel HELP, it doesn't explain that (*).
> > If, say, I use the commas (,) instead, it will falsify the statement.
> >
> >
> >
> >
> >