I'm helping my husband do a spreadsheet for work, and last time I had a
question I came here and I got much needed help but that was a while ago and
now I'm back. <:O)

He works with lieterally hundreds of warehouses and the salesmen need to be
able to see at a glance at what warehouse the product is in. Usually they
have a potential customer on the phone and need quick answers.

I have set up a page that referances ALL the warehouse pages. Each warehouse
has their own page.......

This is what I have
=SUM(IF(Barrett!$E$12:$E$574=$B$4,IF(Barrett!$G$12 :$G$574=$B$5,IF(Barrett!$H$12:$H$574=$B$6,IF(Barre tt!$A$12:$A$574,1,0)))))
Whe
b4 = type of material
b5 = dimension of material
b6 = thickness of material
At first the salesmn were ok with it meeting ALL the criteria, it would "pop
up" but in addition to that, they want to be able to do like a "quick search"
and be able to just enter in 1 of the criteria instead of HAVING to put in
all 3....... They still want to utilize all 3 for when they need the
specifics, but say for example they need a certain type of material but
dimension and thickness doesn't matter... Right now they still need to put
that info in for it to pick up the warehouse. They want to be able to put in
all 3 if they wanted or just 1..... Or say for instance they wanted 4x4
material but the other 2 criteria didn't matter........ How would I go about
doing this??? I have NO IDEA how I would do this so any help you can give
will be greatly appreciated.
Krista

You can do something like this:

=SUM(IF(Barrett!$E$12:$E$574=IF($B$4="",Barrett!$E $12:$E$574,$B$4),.....

If you want to do that for all 3 variables then your formula will pretty
long.

Note that if all 3 variable cells are empty then the formula would return
563. So, you might want to add this to the very beginning of the formula
(making it even longer!!!):

=IF(COUNTA(B4:B6)=0,"",SUM(IF(Barrett!$E$12:$E$574 =IF($B$4="",Barrett!$E$12:$E$574,$B$4),.....


--
Biff
Microsoft Excel MVP


"kristap" wrote in message
...
I'm helping my husband do a spreadsheet for work, and last time I had a
question I came here and I got much needed help but that was a while ago
and
now I'm back. <:O)

He works with lieterally hundreds of warehouses and the salesmen need to
be
able to see at a glance at what warehouse the product is in. Usually they
have a potential customer on the phone and need quick answers.

I have set up a page that referances ALL the warehouse pages. Each
warehouse
has their own page.......

This is what I have
=SUM(IF(Barrett!$E$12:$E$574=$B$4,IF(Barrett!$G$12 :$G$574=$B$5,IF(Barrett!$H$12:$H$574=$B$6,IF(Barre tt!$A$12:$A$574,1,0)))))
Whe
b4 = type of material
b5 = dimension of material
b6 = thickness of material
At first the salesmn were ok with it meeting ALL the criteria, it would
"pop
up" but in addition to that, they want to be able to do like a "quick
search"
and be able to just enter in 1 of the criteria instead of HAVING to put in
all 3....... They still want to utilize all 3 for when they need the
specifics, but say for example they need a certain type of material but
dimension and thickness doesn't matter... Right now they still need to put
that info in for it to pick up the warehouse. They want to be able to put
in
all 3 if they wanted or just 1..... Or say for instance they wanted 4x4
material but the other 2 criteria didn't matter........ How would I go
about
doing this??? I have NO IDEA how I would do this so any help you can give
will be greatly appreciated.
Krista

Hi,

I don't understand the last condtion? However, you can do basically what
you seem to want with a formula of the form:

=IF(SUMPRODUCT((Barrett!$E$12:$E$57=$B$4)+(Barrett !$G$12:$G$57=$B$5)+(Barrett!$H$12:$H$57=$B$6))=CO UNTA(B4:B6),1,0)

In this case if all the conditions that are entered are true then the
formula will return 1 otherwise 0.

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"kristap" wrote:

I'm helping my husband do a spreadsheet for work, and last time I had a
question I came here and I got much needed help but that was a while ago and
now I'm back. <:O)

He works with lieterally hundreds of warehouses and the salesmen need to be
able to see at a glance at what warehouse the product is in. Usually they
have a potential customer on the phone and need quick answers.

I have set up a page that referances ALL the warehouse pages. Each warehouse
has their own page.......

This is what I have
=SUM(IF(Barrett!$E$12:$E$574=$B$4,IF(Barrett!$G$12 :$G$574=$B$5,IF(Barrett!$H$12:$H$574=$B$6,IF(Barre tt!$A$12:$A$574,1,0)))))
Whe
b4 = type of material
b5 = dimension of material
b6 = thickness of material
At first the salesmn were ok with it meeting ALL the criteria, it would "pop
up" but in addition to that, they want to be able to do like a "quick search"
and be able to just enter in 1 of the criteria instead of HAVING to put in
all 3....... They still want to utilize all 3 for when they need the
specifics, but say for example they need a certain type of material but
dimension and thickness doesn't matter... Right now they still need to put
that info in for it to pick up the warehouse. They want to be able to put in
all 3 if they wanted or just 1..... Or say for instance they wanted 4x4
material but the other 2 criteria didn't matter........ How would I go about
doing this??? I have NO IDEA how I would do this so any help you can give
will be greatly appreciated.
Krista

I don't understand the last condtion?
IF(Barrett!$A$12:$A$574

That would be the same as --(rng<0) but that's not very robust.

If you convert the OP's formula to the SUMPRODUCT equivalent:

=SUMPRODUCT(--(E12:E574=B4),--(G12:G574=B5),--(H12:H574=B6),--(ISNUMBER(A12:A574)),--(A12:A574<0))

That doesn't calculate an "OR" condition as your suggestion does.


--
Biff
Microsoft Excel MVP


"Shane Devenshire" wrote in
message ...
Hi,

I don't understand the last condtion? However, you can do basically what
you seem to want with a formula of the form:

=IF(SUMPRODUCT((Barrett!$E$12:$E$57=$B$4)+(Barrett !$G$12:$G$57=$B$5)+(Barrett!$H$12:$H$57=$B$6))=CO UNTA(B4:B6),1,0)

In this case if all the conditions that are entered are true then the
formula will return 1 otherwise 0.

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"kristap" wrote:

I'm helping my husband do a spreadsheet for work, and last time I had a
question I came here and I got much needed help but that was a while ago
and
now I'm back. <:O)

He works with lieterally hundreds of warehouses and the salesmen need to
be
able to see at a glance at what warehouse the product is in. Usually they
have a potential customer on the phone and need quick answers.

I have set up a page that referances ALL the warehouse pages. Each
warehouse
has their own page.......

This is what I have
=SUM(IF(Barrett!$E$12:$E$574=$B$4,IF(Barrett!$G$12 :$G$574=$B$5,IF(Barrett!$H$12:$H$574=$B$6,IF(Barre tt!$A$12:$A$574,1,0)))))
Whe
b4 = type of material
b5 = dimension of material
b6 = thickness of material
At first the salesmn were ok with it meeting ALL the criteria, it would
"pop
up" but in addition to that, they want to be able to do like a "quick
search"
and be able to just enter in 1 of the criteria instead of HAVING to put
in
all 3....... They still want to utilize all 3 for when they need the
specifics, but say for example they need a certain type of material but
dimension and thickness doesn't matter... Right now they still need to
put
that info in for it to pick up the warehouse. They want to be able to put
in
all 3 if they wanted or just 1..... Or say for instance they wanted 4x4
material but the other 2 criteria didn't matter........ How would I go
about
doing this??? I have NO IDEA how I would do this so any help you can give
will be greatly appreciated.
Krista

I don't understand why would an empty cell produce 563?

My husband is still at work so I can't log on remotely until he is home but
I will try this suggestion later on this evening, hopefully it will work.

You said that if ALL 3 cells were empty it would return 563, but if the
salesmen used atleast 1 of the cells, would it return the correct warehouse,
or do I still need to add that stuff to the beginning if they will be adding
atleast 1 cell?

Krista

"T. Valko" wrote:

You can do something like this:

=SUM(IF(Barrett!$E$12:$E$574=IF($B$4="",Barrett!$E $12:$E$574,$B$4),.....

If you want to do that for all 3 variables then your formula will pretty
long.

Note that if all 3 variable cells are empty then the formula would return
563. So, you might want to add this to the very beginning of the formula
(making it even longer!!!):

=IF(COUNTA(B4:B6)=0,"",SUM(IF(Barrett!$E$12:$E$574 =IF($B$4="",Barrett!$E$12:$E$574,$B$4),.....


--
Biff
Microsoft Excel MVP


"kristap" wrote in message
...
I'm helping my husband do a spreadsheet for work, and last time I had a
question I came here and I got much needed help but that was a while ago
and
now I'm back. <:O)

He works with lieterally hundreds of warehouses and the salesmen need to
be
able to see at a glance at what warehouse the product is in. Usually they
have a potential customer on the phone and need quick answers.

I have set up a page that referances ALL the warehouse pages. Each
warehouse
has their own page.......

This is what I have
=SUM(IF(Barrett!$E$12:$E$574=$B$4,IF(Barrett!$G$12 :$G$574=$B$5,IF(Barrett!$H$12:$H$574=$B$6,IF(Barre tt!$A$12:$A$574,1,0)))))
Whe
b4 = type of material
b5 = dimension of material
b6 = thickness of material
At first the salesmn were ok with it meeting ALL the criteria, it would
"pop
up" but in addition to that, they want to be able to do like a "quick
search"
and be able to just enter in 1 of the criteria instead of HAVING to put in
all 3....... They still want to utilize all 3 for when they need the
specifics, but say for example they need a certain type of material but
dimension and thickness doesn't matter... Right now they still need to put
that info in for it to pick up the warehouse. They want to be able to put
in
all 3 if they wanted or just 1..... Or say for instance they wanted 4x4
material but the other 2 criteria didn't matter........ How would I go
about
doing this??? I have NO IDEA how I would do this so any help you can give
will be greatly appreciated.
Krista

I don't understand why would an empty cell produce 563?

Because what the formula does is if one of the variable cells is empty it
counts every one of the cells for that particular variable. So, if all 3
variable cells are empty the result of the formula will be the count of the
total number of rows referenced.

I imagine that you'll always start with empty variable cells so I'd leave
that first part of the formula in there.

--
Biff
Microsoft Excel MVP


"kristap" wrote in message
...
I don't understand why would an empty cell produce 563?

My husband is still at work so I can't log on remotely until he is home
but
I will try this suggestion later on this evening, hopefully it will work.

You said that if ALL 3 cells were empty it would return 563, but if the
salesmen used atleast 1 of the cells, would it return the correct
warehouse,
or do I still need to add that stuff to the beginning if they will be
adding
atleast 1 cell?

Krista

"T. Valko" wrote:

You can do something like this:

=SUM(IF(Barrett!$E$12:$E$574=IF($B$4="",Barrett!$E $12:$E$574,$B$4),.....

If you want to do that for all 3 variables then your formula will pretty
long.

Note that if all 3 variable cells are empty then the formula would return
563. So, you might want to add this to the very beginning of the formula
(making it even longer!!!):

=IF(COUNTA(B4:B6)=0,"",SUM(IF(Barrett!$E$12:$E$574 =IF($B$4="",Barrett!$E$12:$E$574,$B$4),.....


--
Biff
Microsoft Excel MVP


"kristap" wrote in message
...
I'm helping my husband do a spreadsheet for work, and last time I had a
question I came here and I got much needed help but that was a while
ago
and
now I'm back. <:O)

He works with lieterally hundreds of warehouses and the salesmen need
to
be
able to see at a glance at what warehouse the product is in. Usually
they
have a potential customer on the phone and need quick answers.

I have set up a page that referances ALL the warehouse pages. Each
warehouse
has their own page.......

This is what I have
=SUM(IF(Barrett!$E$12:$E$574=$B$4,IF(Barrett!$G$12 :$G$574=$B$5,IF(Barrett!$H$12:$H$574=$B$6,IF(Barre tt!$A$12:$A$574,1,0)))))
Whe
b4 = type of material
b5 = dimension of material
b6 = thickness of material
At first the salesmn were ok with it meeting ALL the criteria, it would
"pop
up" but in addition to that, they want to be able to do like a "quick
search"
and be able to just enter in 1 of the criteria instead of HAVING to put
in
all 3....... They still want to utilize all 3 for when they need the
specifics, but say for example they need a certain type of material but
dimension and thickness doesn't matter... Right now they still need to
put
that info in for it to pick up the warehouse. They want to be able to
put
in
all 3 if they wanted or just 1..... Or say for instance they wanted 4x4
material but the other 2 criteria didn't matter........ How would I go
about
doing this??? I have NO IDEA how I would do this so any help you can
give
will be greatly appreciated.
Krista