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

[0-0 Wai Wai ^-^]

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?

 

[0-0 Wai Wai ^-^]

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.

 

[dominicb]

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

 

[Bob Phillips]

--

HTH

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

 

[Bob Phillips]

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)

 

[RagDyeR]

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!
=====================================================

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.

 

[0-0 Wai Wai ^-^]

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

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")*P2934)

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

 

[RagDyeR]

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!
=====================================================


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

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")*P2934)

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

 

[0-0 Wai Wai ^-^]

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.

 

[aaron.kempf]

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

 

[RagDyeR]

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!
=====================================================

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.

 

[Curt]

(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.

 

[Bob Phillips]

=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)

 

[Don Guillett]

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

 

[RagDyer]

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!
==============================================