Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
need help with nested formulas please
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
need help with nested formulas please
ONe way:
=SUMPRODUCT(--(A1:A10="Steel"),--(F1:F10="Square")) HTH, Paul -- wrote in message ups.com... 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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
need help with nested formulas please
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 " wrote: 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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
need help with nested formulas please
Thank you both Elkar and Paul, your tips helped me greatly!
best, Pmack |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Nested-IF formulas | Excel Discussion (Misc queries) | |||
how to do nested IF formulas | Excel Discussion (Misc queries) | |||
help in nested if formulas | Excel Worksheet Functions | |||
if nested formulas | Excel Discussion (Misc queries) | |||
nested formulas | Excel Worksheet Functions |