I have two tabs in a spreadsheet (DOG & CAT) and have the following function
set up to compare 3 questions, ie. only if all three equal the value required
is the corresponding values counted.

=COUNT(IF(DOG!J6:J1000=CAT!J7,IF(DIVA!S6:S1000=CAT !A12,IF(DOG!T6:T1000="0",DOG!B6:B1000,0))))

It's not working and returning strange amounts. It needs to look at the dog
spreadsheet and compare the 3 different questions. How can I rearrange this
to work?

"Rosie" ha scritto nel messaggio
...
I have two tabs in a spreadsheet (DOG & CAT) and have the following
function
set up to compare 3 questions, ie. only if all three equal the value
required
is the corresponding values counted.

=COUNT(IF(DOG!J6:J1000=CAT!J7,IF(DIVA!S6:S1000=CAT !A12,IF(DOG!T6:T1000="0",DOG!B6:B1000,0))))

It's not working and returning strange amounts. It needs to look at the
dog
spreadsheet and compare the 3 different questions. How can I rearrange
this
to work?

Hi Rosie,

I think you could try with SUMPRODUCT function:

=SUMPRODUCT((DOG!J6:J1000=CAT!J7)*(DIVA!S6:S1000=C AT!A12)*(DOG!T6:T1000="0")*(DOG!B6:B1000))



--
Hope I helped you.

Thanks in advance for your feedback.

Ciao

Franz Verga from Italy

Hi!

A couple of questions:

Are there any empty cells in DOG!T6:T1000 ?

Is the data in DOG!T6:T1000 TEXT or NUMERIC ?

Try this:

=SUMPRODUCT(--(ISNUMBER(DOG!B6:B1000)),--(DOG!J6:J1000=CAT!J7),--(DOG!T6:T1000=0),--(DIVA!S6:S1000=CAT!A12))

I've assumed the data in DOG!T6:T1000 is NUMERIC. You'll notice that I
removed the " " from around the 0. Enclosing a NUMBER in quotes makes it a
TEXT value.

Also, if DOG!T6:T1000 contains empty cells you'll have to add another array:

=SUMPRODUCT(--(ISNUMBER(DOG!B6:B1000)),--(DOG!J6:J1000=CAT!J7),--(ISNUMBER(DOG!T6:T1000)),--(DOG!T6:T1000=0),--(DIVA!S6:S1000=CAT!A12))

Biff

"Rosie" wrote in message
...
I have two tabs in a spreadsheet (DOG & CAT) and have the following
function
set up to compare 3 questions, ie. only if all three equal the value
required
is the corresponding values counted.

=COUNT(IF(DOG!J6:J1000=CAT!J7,IF(DIVA!S6:S1000=CAT !A12,IF(DOG!T6:T1000="0",DOG!B6:B1000,0))))

It's not working and returning strange amounts. It needs to look at the
dog
spreadsheet and compare the 3 different questions. How can I rearrange
this
to work?

Thanks but unfortunately didn't work. There are no empty cells in T and it is
a 0 or 1 response (linked from Access dbase record).

What I'm comparing is three fields:
1. Text Field (match the correct location)
2. Numeric. field (match the correct bedroom category)
3. Numeric (is it a zero (nil) or 1 (yes)

Nothing has worked to date...

"Biff" wrote:

Hi!

A couple of questions:

Are there any empty cells in DOG!T6:T1000 ?

Is the data in DOG!T6:T1000 TEXT or NUMERIC ?

Try this:

=SUMPRODUCT(--(ISNUMBER(DOG!B6:B1000)),--(DOG!J6:J1000=CAT!J7),--(DOG!T6:T1000=0),--(DIVA!S6:S1000=CAT!A12))

I've assumed the data in DOG!T6:T1000 is NUMERIC. You'll notice that I
removed the " " from around the 0. Enclosing a NUMBER in quotes makes it a
TEXT value.

Also, if DOG!T6:T1000 contains empty cells you'll have to add another array:

=SUMPRODUCT(--(ISNUMBER(DOG!B6:B1000)),--(DOG!J6:J1000=CAT!J7),--(ISNUMBER(DOG!T6:T1000)),--(DOG!T6:T1000=0),--(DIVA!S6:S1000=CAT!A12))

Biff

"Rosie" wrote in message
...
I have two tabs in a spreadsheet (DOG & CAT) and have the following
function
set up to compare 3 questions, ie. only if all three equal the value
required
is the corresponding values counted.

=COUNT(IF(DOG!J6:J1000=CAT!J7,IF(DIVA!S6:S1000=CAT !A12,IF(DOG!T6:T1000="0",DOG!B6:B1000,0))))

It's not working and returning strange amounts. It needs to look at the
dog
spreadsheet and compare the 3 different questions. How can I rearrange
this
to work?

Can you send me a copy of the file? If you're importing data from another
application there is a possibilty that it's also importing some unseen
"junk" with it. Like leading and/or trailing spaces.

If you want to I'll take a look see. I'm at:

xl can help at comcast period net

Remove "can" and change the obvious.

Biff

"Rosie" wrote in message
...
Thanks but unfortunately didn't work. There are no empty cells in T and it
is
a 0 or 1 response (linked from Access dbase record).

What I'm comparing is three fields:
1. Text Field (match the correct location)
2. Numeric. field (match the correct bedroom category)
3. Numeric (is it a zero (nil) or 1 (yes)

Nothing has worked to date...

"Biff" wrote:

Hi!

A couple of questions:

Are there any empty cells in DOG!T6:T1000 ?

Is the data in DOG!T6:T1000 TEXT or NUMERIC ?

Try this:

=SUMPRODUCT(--(ISNUMBER(DOG!B6:B1000)),--(DOG!J6:J1000=CAT!J7),--(DOG!T6:T1000=0),--(DIVA!S6:S1000=CAT!A12))

I've assumed the data in DOG!T6:T1000 is NUMERIC. You'll notice that I
removed the " " from around the 0. Enclosing a NUMBER in quotes makes it
a
TEXT value.

Also, if DOG!T6:T1000 contains empty cells you'll have to add another
array:

=SUMPRODUCT(--(ISNUMBER(DOG!B6:B1000)),--(DOG!J6:J1000=CAT!J7),--(ISNUMBER(DOG!T6:T1000)),--(DOG!T6:T1000=0),--(DIVA!S6:S1000=CAT!A12))

Biff

"Rosie" wrote in message
...
I have two tabs in a spreadsheet (DOG & CAT) and have the following
function
set up to compare 3 questions, ie. only if all three equal the value
required
is the corresponding values counted.

=COUNT(IF(DOG!J6:J1000=CAT!J7,IF(DIVA!S6:S1000=CAT !A12,IF(DOG!T6:T1000="0",DOG!B6:B1000,0))))

It's not working and returning strange amounts. It needs to look at the
dog
spreadsheet and compare the 3 different questions. How can I rearrange
this
to work?