CountIF

[rexmann]

Hi All

I have a question relating to the CountIF function. I have a need for a
function which uses countIF but the value meets a criteria from 2 columns (or
alternatively has two sets of criteria)

I can get it to work for one column but not two
=COUNTIF('column if data '!D2463,"=Yes")

I have tried to add (and 'column of data2 '!E2:E462," =No") and various
variations of the above but no joy

Do you have any suggestions (maybe a countIF is not the way to go)

Any help greatly appreciated

Cheers Rexmann

 

[Roger Govier]

Hi

You can use multiple Countif only in XL2007 with the COUNTIFS function.

Try
=SUMPRODUCT(('column if data '!D2463,"=Yes")*('column if data
'!E2:E463,"=No"))

Note the ranges have to be of equal length (perhaps you had a typo in your
posting)

 

[Gav123]

Hi,

Maybe you could try a SUMPRODUCT function, some thing like..

=SUMPRODUCT(--(Range1=criteria1),--(Range2=criteria2))

Hope this helps.

Gav.

 

[Greg]

Hi GAV123.

I'm trying to use your formula and am having an issue with it. My formula is:

=SUMPRODUCT(--('9.1 ADMITS'!A:A=Sheet1!A2),--('9.1 ADMITS'!J:J='9.1
ADMITS'!F:F))

But I keep getting a #NUM! error. Any idea what I am doing wrong?

Excel 2003

TIA,

Greg

 

[Dave Peterson]

You can't use the entire column in xl2003. Adjust your ranges to be less than
the entire column, but make sure they're the same size.

 

[Greg]

Hi Dave.

Thank you for the quick response. I tried changing it, but it's not
working. I'm using:

=SUMPRODUCT(--('9.1 ADMITS'!$A$1:$A$1000=Sheet1!A2),--('9.1
ADMITS'!$J$1:$J$1000='9.1 ADMITS'!$F$1:$F$1000))

It is only bringing back some, but not all of the correct answers.

I believe the first part of the equation is correct, because when I run:

=COUNTIF('9.1 ADMITS'!A:A,Sheet1!A16)

It works fine and accurately.

Columns F & J have been formatted as dates if that matters???

TIA,

Greg

 

[Dave Peterson]

The formula is fine, but your data isn't.

And it's not simply the cells being formatted as dates that's the problem. But
that could be hiding the problem.

If your cells contain dates and times, but you've formatted the cells to only
show the dates, then the cells may look the same, but they may be different
because of the times.

If the values in some of the cells are really text, but look like dates, then
they may not be equal. A real date of Jan 1, 2009 formatted to look like
01/01/2009 will not be equal to a cell containing the text: '01/01/2009

If I were you, I'd verify that my dates are really dates--change the format of
both columns to something unambiguous:

mmmm dd, yyyy hh:mm:ss

If any of the cells doesn't change what's displayed, then those cells aren't
really dates.

One more thing to check...

If you've imported the data from a different source, your dates could be text:
01/31/2009 may have been treated as text if your short date format is
dd/mm/yyyy.

And even worse:
01/02/03
may not be the date that you think it is. It could be Jan 2, 2003 or Feb 1,
2003 or lots of other reasonable interpretations.

 

[Greg]

Dave, you're a genius!

OK, I'm almost done with my new spreadsheet, but have one more thing I can't
figure out and wondered if you might help? (or anyone for that matter).

Formula is:

=SUMPRODUCT(--('9.5 ADMITS'!$A1:$A1000=A3),--('9.5 ADMITS'!$J1:$J1000<'9.5
ADMITS'!$F1:$F1000))

But the problem is it is bringing back a result that includes null cells.
How can I avoid nulls?

Thank you very much for all your help.

Greg

 

[Dave Peterson]

So if A3="", you don't want to count???

=if(a3="","no data",sumproduct(..yourformulahere...))

Change the "no data" to what you want ("" or 0 or ...)

If you're looking to make sure taht J1:J1000 is numeric, you can add another
criteria:

=SUMPRODUCT(--('9.5 ADMITS'!$A1:$A1000=A3),
--(isnumber('9.5 admits'!$j$1:$j$1000),
--('9.5 ADMITS'!$J1:$J1000<'9.5 ADMITS'!$F1:$F1000))

Keep adding more criteria if you need to--like for column F???

Dave, you're a genius!

OK, I'm almost done with my new spreadsheet, but have one more thing I can't
figure out and wondered if you might help? (or anyone for that matter).

Formula is:

=SUMPRODUCT(--('9.5 ADMITS'!$A1:$A1000=A3),--('9.5 ADMITS'!$J1:$J1000<'9.5
ADMITS'!$F1:$F1000))

But the problem is it is bringing back a result that includes null cells.
How can I avoid nulls?

Thank you very much for all your help.

Greg

 

[Greg]

That's great. Thank you very much.

I appreciate all the help.

Greg