How do I set up a countif, or sumif that is multiconditional?
 

I often try to set up a countif or a sum if that is conditional on two or
more conditions. For instance - I have Radio stations listed in one column,
flight dates in another column, number of contacts for each flight in another
column and appointments created from those contacts and finally, revenue
generated form those appointments. One example would be to sumif column one
equals the radio station and column two equaled the flight date then give me
the revenue.

How do I set up a countif, or sumif that is multiconditional?
 

Hy Yanni

try to use

=sumproduct(--($a$2:$a$100="radio")*($B$2:$b$100=date)*($c$2:$c$ 100))
assuming that the revenue are on the C column

hope this helps
regards from Brazil
Marcelo

"Mr. Yanni" escreveu:

I often try to set up a countif or a sum if that is conditional on two or
more conditions. For instance - I have Radio stations listed in one column,
flight dates in another column, number of contacts for each flight in another
column and appointments created from those contacts and finally, revenue
generated form those appointments. One example would be to sumif column one
equals the radio station and column two equaled the flight date then give me
the revenue.

How do I set up a countif, or sumif that is multiconditional?
 

"Marcelo" wrote:

Hy Yanni

try to use

=sumproduct(--($a$2:$a$100="radio")*($B$2:$b$100=date)*($c$2:$c$ 100))
assuming that the revenue are on the C column

hope this helps
regards from Brazil
Marcelo

"Mr. Yanni" escreveu:

I often try to set up a countif or a sum if that is conditional on two or
more conditions. For instance - I have Radio stations listed in one column,
flight dates in another column, number of contacts for each flight in another
column and appointments created from those contacts and finally, revenue
generated form those appointments. One example would be to sumif column one
equals the radio station and column two equaled the flight date then give me
the revenue.

I did not know such a function existed. I will give it a try. Thank you my
brazilian friend. gracious.

How do I set up a countif, or sumif that is multiconditional?
 

Hi

Take a look at the Sumproduct() function
=SUMPRODUCT(--($A$1:$A$100="Station1"),--($B$1:$B$100=DATE(2006,6,20)),$C$1:$C$100)

This assumes that your Radio Station is in column A, your Flight Dates
in column B and your Revenue in column C.
Change ranges to suit, but do ensure that the ranges are of equal size.
Substitute for Station 1, the name of the station you want and for the
date (example shows 20th June 2006)

--
Regards

Roger Govier


"Mr. Yanni" <Mr. wrote in message
...
I often try to set up a countif or a sum if that is conditional on two
or
more conditions. For instance - I have Radio stations listed in one
column,
flight dates in another column, number of contacts for each flight in
another
column and appointments created from those contacts and finally,
revenue
generated form those appointments. One example would be to sumif
column one
equals the radio station and column two equaled the flight date then
give me
the revenue.

How do I set up a countif, or sumif that is multiconditional?
 

You are welcome Mr Yanni

"Mr. Yanni" escreveu:



"Marcelo" wrote:

Hy Yanni

try to use

=sumproduct(--($a$2:$a$100="radio")*($B$2:$b$100=date)*($c$2:$c$ 100))
assuming that the revenue are on the C column

hope this helps
regards from Brazil
Marcelo

"Mr. Yanni" escreveu:

I often try to set up a countif or a sum if that is conditional on two or
more conditions. For instance - I have Radio stations listed in one column,
flight dates in another column, number of contacts for each flight in another
column and appointments created from those contacts and finally, revenue
generated form those appointments. One example would be to sumif column one
equals the radio station and column two equaled the flight date then give me
the revenue.

I did not know such a function existed. I will give it a try. Thank you my
brazilian friend. gracious.

How do I set up a countif, or sumif that is multiconditional?
 

"Marcelo" wrote:

Hy Yanni

try to use

=sumproduct(--($a$2:$a$100="radio")*($B$2:$b$100=date)*($c$2:$c$ 100))
assuming that the revenue are on the C column

hope this helps
regards from Brazil
Marcelo

"Mr. Yanni" escreveu:

I often try to set up a countif or a sum if that is conditional on two or
more conditions. For instance - I have Radio stations listed in one column,
flight dates in another column, number of contacts for each flight in another
column and appointments created from those contacts and finally, revenue
generated form those appointments. One example would be to sumif column one
equals the radio station and column two equaled the flight date then give me
the revenue.
Just tried it out - it works.

How do I set up a countif, or sumif that is multiconditional?
 

"Roger Govier" wrote:

Hi

Take a look at the Sumproduct() function
=SUMPRODUCT(--($A$1:$A$100="Station1"),--($B$1:$B$100=DATE(2006,6,20)),$C$1:$C$100)

This assumes that your Radio Station is in column A, your Flight Dates
in column B and your Revenue in column C.
Change ranges to suit, but do ensure that the ranges are of equal size.
Substitute for Station 1, the name of the station you want and for the
date (example shows 20th June 2006)

--
Regards

Roger Govier


"Mr. Yanni" <Mr. wrote in message
...
I often try to set up a countif or a sum if that is conditional on two
or
more conditions. For instance - I have Radio stations listed in one
column,
flight dates in another column, number of contacts for each flight in
another
column and appointments created from those contacts and finally,
revenue
generated form those appointments. One example would be to sumif
column one
equals the radio station and column two equaled the flight date then
give me
the revenue.
Ok, it seems that the final array automatically sums under these conditions...How can I use the same formula to countif the first two or more conditions exist?

How do I set up a countif, or sumif that is multiconditional?
 

Hi! I have a similar query, i know how to use the sumproduct function in
rgard to my data, what i want to know is how to refer to column in another
sheet.
Thanks

How do I set up a countif, or sumif that is multiconditional?
 

Hi Mr Yanni

Ok, it seems that the final array automatically sums under these
conditions...How can I use the same formula to countif the first two
or more conditions exist?

Just leave out the final Revenue range
=SUMPRODUCT(--($A$1:$A$100="Station1"),--($B$1:$B$100=DATE(2006,6,20)))

--
Regards

Roger Govier


"Mr. Yanni" wrote in message
...


"Roger Govier" wrote:

Hi

Take a look at the Sumproduct() function
=SUMPRODUCT(--($A$1:$A$100="Station1"),--($B$1:$B$100=DATE(2006,6,20)),$C$1:$C$100)

This assumes that your Radio Station is in column A, your Flight
Dates
in column B and your Revenue in column C.
Change ranges to suit, but do ensure that the ranges are of equal
size.
Substitute for Station 1, the name of the station you want and for
the
date (example shows 20th June 2006)

--
Regards

Roger Govier


"Mr. Yanni" <Mr. wrote in message
...
I often try to set up a countif or a sum if that is conditional on
two
or
more conditions. For instance - I have Radio stations listed in
one
column,
flight dates in another column, number of contacts for each flight
in
another
column and appointments created from those contacts and finally,
revenue
generated form those appointments. One example would be to sumif
column one
equals the radio station and column two equaled the flight date
then
give me
the revenue.
Ok, it seems that the final array automatically sums under these
conditions...How can I use the same formula to countif the first two
or more conditions exist?

How do I set up a countif, or sumif that is multiconditional?
 

Hi Jess

Just prefix the range with the sheet name and an exclamation mark e.g.

Sheet1!$A$1:$A$100
'My Data'!$A$1:$A$100

Note that if you have spaces in the sheet name, you must enclose the
name within single quotes.

--
Regards

Roger Govier


"Jess" wrote in message
...
Hi! I have a similar query, i know how to use the sumproduct function
in
rgard to my data, what i want to know is how to refer to column in
another
sheet.
Thanks