The easiest way to solve this would be to send me a copy of the file.

Are there any #N/A errors in any of your ranges?

I have to ask you about your time criteria:

0.067 (time)
0.167 (time)

As I noted in my other reply:

0.067 = 1:36:29 AM
0.167 = 4:00:29 AM

Are those the actual time values that you want to use?

If there is a time entered as 4:00:29 AM then this test will fail because of
rounding:

0.167<=4:00:29 AM

The true value of 4:00:29 AM is 0.167002314814815

That being said, that issue will not cause an #N/A error.

If you can send me a copy of the file I'm at:

xl can help at comcast period net

Remove "can" and change the obvious.

Note that I use Excel 2002. Don't send a file in Excel 2007 format.



--
Biff
Microsoft Excel MVP


"thomsonpa" wrote in message
...
I used this formula, but receive an #N/A now. I nested the formula, but
still
received an #N/A.


"T. Valko" wrote:

Does that make sense?

Yes. Try it like this:

=SUMPRODUCT(--(Import!$Q$2:$Q$86="FB"),--(Import!$Z$2:$Z$86="ME"),--(Import!$A$2:$A$86=0.067),--(Import!$A$2:$A$86<=0.167),Import!$I$2:$I$86)

Don't get "locked in" to thinking you "have to" use IF.


--
Biff
Microsoft Excel MVP


"thomsonpa" wrote in message
...

Many thanks, you are right I got my less than and more than the wrong
way
around. However, I don't think I have explained my problem correctly.
I
am
looking for the solution to an "IF" problem between 2 time ranges. I
have
all the information listed in columns, ie. some columns have HB, some
have
ME, all have different times, and some cells contain figures (some
blank).
I
need to look in one column for HB with another column in the same row
containing ME in the rows between the 2 times, adding up the figures in
another column. Does that make sense?



"T. Valko" wrote:

This does what you asked for:

And the result will always be 0. The problem is your logic:

I am looking for <0.067 and 0.167...(time)

A time can never be <0.067 and 0.167.

Are you sure that's what you want? I'm guessing that what you really
meant
is:

0.067 and <0.167

Or:

=0.067 and <=0.167



--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
Hmmm...

I am looking for <0.067 and 0.167...(time)

0.067 = 1:36:29 AM
0.167 = 4:00:29 AM

This does what you asked for:

=SUMPRODUCT(--(Import!$Q$2:$Q$86="FB"),--(Import!$Z$2:$Z$86="ME"),--(Import!$A$2:$A$86<=0.067),--(Import!$A$2:$A$86=0.167),Import!$I$2:$I$86)



--
Biff
Microsoft Excel MVP


"thomsonpa" wrote in message
...
I have a nested if formula, where I am looking for the total of the
values
in
a range of cells which match 4 ranges of criteria. Total number in
cells
listed as "FB" and "ME" less than 0.067 (time) and more than 0.167
(time).
The lists are on a seperate worksheet. I can get the sum to work
with 3
criteria, ie. only less than time in one formula and more than time
in
another, but not with both.
Any help would be appreciated as I am a novice at this. Here is my
formula
for one time frame.
=SUM(IF(IMPORT!$Q$2:$Q$86="FB",IF(IMPORT!$Z$2:$Z$8 6="ME",IF(IMPORT!$A$2:$A$86<=0.167,IMPORT!$I$2:$I$ 86,0),0),0))+SUM(IF(IMPORT!$R$2:$R$86="FB",IF(IMPO RT!$Z$2:$Z$86="ME",IF(IMPORT!$A$2:$A$86<=0.167,IMP ORT!$I$2:$I$86,0),0),0))+SUM(IF(IMPORT!$S$2:$S$86= "FB",IF(IMPORT!$Z$2:$Z$86="ME",IF(IMPORT!$A$2:$A$8 6<=0.167,IMPORT!$I$2:$I$86,0),0),0)

I am looking for <0.067 and 0.167 in the range IMPORT!$I$2:$I$86.