need help with nested formulas please

[pmackscans]

Hello--

I'm only familiar with Excel on a very basic level, and I'm stuck with
two different problems I can't solve.

1) I have an inventory sheet with details about a product. I use
similar formulas to give me totals from two different columns:

=COUNTIF(A1:A10,"Steel")

=COUNTIF(F1:F10,"Square")

What I would like to do is filter the results of the first formula
("Steel") with the second formula ("Square"), so that I will get a
total of products that are made of steel and are also square.

I realize this is probably a simple question, but I just can't seem to
figure it out. All the examples I find involve counting numeric
values, and the formulas I have tried fail when I try and count text
values instead.

2) My second problem is a divide by zero issue. The formula I use to
tell me what percentage of my total inventory I have on my floor is

=SUM(Local_Inventory!D6/Total_Inventory!D5)

It works fine when either cell has a value greater than 0, but on the
rare occasion that both are zero (like when I'm completely sold out of
a product), is there some way to get the worksheet to display a zero
instead of #DIV/0! ?

Any help is greatly appreciated, thanks in advance!
Dave

 

[PCLIVE]

ONe way:

=SUMPRODUCT(--(A1:A10="Steel"),--(F1:F10="Square"))

HTH,
Paul

 

[Guest]

For your first question, try this:

=SUMPRODUCT(--(A1:A10="Steel"),--(F1:F10="Square"))

For your second question:

=IF(Total_Inventory!D5=0,0,Local_Inventory!D6/Total_Inventory!D5)

HTH,
Elkar

 

[pmackscans]

Thank you both Elkar and Paul, your tips helped me greatly!

best,
Pmack