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

G

Guest

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

Guest

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:
 
G

Guest

Marcelo said:
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:
Click to expand...

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

Roger Govier

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

Guest

Marcelo said:
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:
Click to expand...
Just tried it out - it works.
 
G

Guest

Roger Govier said:
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



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?
Click to expand...
 
G

Guest

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
 
R

Roger Govier

Hi Mr Yanni

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

Roger Govier

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

SUMIF/COUNTIF with an additional criteria 1
How do I set up a nested SUMIF formula for a number of variables? 2
How do I add a second criteria to the SUMIF or DSUM function? 2
Sumifs (I think) 5
How do I use the criteria "is not blank" in a SUMIF formula? 2
How do I write a comlicated conditional statement? 3
How do I set up a function in excel to give either a <> or =? 3
for SUMIF function, how do I use 2 sets of range & criteria 6

Top