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.

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.

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

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. [email protected]> wrote in message
news:[email protected]...
>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.

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.

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

"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. [email protected]> wrote in message
> news:[email protected]...
> >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?
>
>

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

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" <[email protected]> wrote in message
news:[email protected]...
>
>
> "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. [email protected]> wrote in message
>> news:[email protected]...
>> >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?
>>
>>

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" <[email protected]> wrote in message
news:[email protected]...
> 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