Really need to do this!

[ivera]

Hey guys,

This is something I need for my work. Ive been trying to get around it but
can't seem to make it work.

I need to know how many people are in Column A are from range >=1 <=2 if
Column B is <=5 all this if Column C=E5 (just an example).

Thanks.
[email protected]

 

[ivera]

Mike,

Thanks for the help, I tried editing that function to my needs but then I
got some errors, also I found I need 1 or 2 more constraints, here's a
similar example of what I need:

IF F:F=000, G:G=AA and K:K>=5 then COUNT how many H:H are between 5 and 7
(including 5 and 7)

I hope this can be done with functions.

Thanks.

 

[David Biddulph]

=SUMPRODUCT((F1:F100="000")*(G1:G100="AA")*(K1:K100>=5)*(H1:H100>=5)*(H1:H100<=7))

[If you need help debugging your formula, it's no good just saying "I got
some errors", as our crystal balls sometimes get cloudy and we can't always
guess what you've done. It helps if you tell us exactly what error message
you receive, or what results you get for which inputs with exactly which
formula.]

 

[ivera]

David,

You'are right, I was way too cloudy...thanks for the help. This time I
pasted the function put my columns and conditions, now instead of an error I
just get a zero "0". And I know there's data.

I've been considering trying to use access for this, I exported the file to
access created, table, queries etc...but I have no clue of how to connect
excel to access! How hard is it?

Ivan

=SUMPRODUCT((F1:F100="000")*(G1:G100="AA")*(K1:K100>=5)*(H1:H100>=5)*(H1:H100<=7))

[If you need help debugging your formula, it's no good just saying "I got
some errors", as our crystal balls sometimes get cloudy and we can't always
guess what you've done. It helps if you tell us exactly what error message
you receive, or what results you get for which inputs with exactly which
formula.]
--
David Biddulph

Mike,

Thanks for the help, I tried editing that function to my needs but then I
got some errors, also I found I need 1 or 2 more constraints, here's a
similar example of what I need:

IF F:F=000, G:G=AA and K:K>=5 then COUNT how many H:H are between 5 and 7
(including 5 and 7)

I hope this can be done with functions.

Thanks.

 

[David Biddulph]

For anyone getting what they think is the wrong answer from a long formula,
the best bet is to break it down into manageable chunks. In your case you
can test each of the conditions separately:
=F1="000"
=G1="AA"
=K1>=5
etc.

One possible cause for error is if your 000 is not a text string, but a
number formatted as 000, in which omit the quotes around "000" in my
formula.
--
David Biddulph

David,

You'are right, I was way too cloudy...thanks for the help. This time I
pasted the function put my columns and conditions, now instead of an error
I
just get a zero "0". And I know there's data.

I've been considering trying to use access for this, I exported the file
to
access created, table, queries etc...but I have no clue of how to connect
excel to access! How hard is it?

Ivan

=SUMPRODUCT((F1:F100="000")*(G1:G100="AA")*(K1:K100>=5)*(H1:H100>=5)*(H1:H100<=7))

[If you need help debugging your formula, it's no good just saying "I got
some errors", as our crystal balls sometimes get cloudy and we can't
always
guess what you've done. It helps if you tell us exactly what error
message
you receive, or what results you get for which inputs with exactly which
formula.]
--
David Biddulph

Mike,

Thanks for the help, I tried editing that function to my needs but then
I
got some errors, also I found I need 1 or 2 more constraints, here's a
similar example of what I need:

IF F:F=000, G:G=AA and K:K>=5 then COUNT how many H:H are between 5 and
7
(including 5 and 7)

I hope this can be done with functions.

Thanks.

:

try

=SUMPRODUCT((B1:B10<=5)*(C1:C10=E5)*(A1:A10=1))+SUMPRODUCT((B1:B10<=5)*(C1:C10=E5)*(A1:A10=2))

Mike

:

Hey guys,

This is something I need for my work. Ive been trying to get around
it
but
can't seem to make it work.

I need to know how many people are in Column A are from range >=1
<=2
if
Column B is <=5 all this if Column C=E5 (just an example).

Thanks.
[email protected]