Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formulas Still Not Working
I am so sorry to bother everyone, but would love it if someone could
contunie to help me with these formulas. I made the initial inquiry below, and Isaeblle provided me with formulas (so appreciate the help), but they are not working in my spreadsheet. I am not an excel person at all - a coworker was terminated, and my boss has asked me to help her put calcuations into a spreadsheet that is due to accounting this afternoon. I was referred to this site as a possible solution. I am using Excel 07. =SUMPRODUCT(--(A2:A20="Open - Active")*(B2:B20<"Gvt Invest")*(C2:C120)) =SUMPRODUCT(--(A2:A20="Open - Active")*(B2:B20="Gvt Invest")*(C2:C120)) =SUMPRODUCT(--(A2:A20="Open - Probable")*(C2:C120)) See example and explanation below: STATUS NAT OF CASE RSV Open - Active EEOC 1000 Open - Probable EEOC 1000 Open - Active GVT Invest 1000 Open - Probable Gvt Invest 2000 How do I create formulas that will capture the subtotals totals for J for: Open - Active (Non - Government Investigations): Total all in spreadsheet that are Open - Active but not classified as Gvt invest. Total should equal 1000 Open - Active (Government Investigations): Total all in spreadsheet that are Open - Active but classified as Gvt invest. only. Total should equal 1000 Open - Probable: Totals all in spreadsheet that are Open - Probable. Total should equal 3000 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formulas Still Not Working
"not working" means what? Wrong answer? Nothing? Error?
I tested my own and Isabelle's formulas on your sample data. Return all the values you ask for in your 3 scenarios 1000, 1000, 3000 In your first post you showed your data in columns F, G and H I based my formula on that sample. Where is your data? Use a range when posting back. By range I mean A2:C20 or F2:H10 or similar. We are assuming you have a header row for titles. Gord On Fri, 6 Jan 2012 11:08:35 -0800 (PST), Michelle wrote: I am so sorry to bother everyone, but would love it if someone could contunie to help me with these formulas. I made the initial inquiry below, and Isaeblle provided me with formulas (so appreciate the help), but they are not working in my spreadsheet. I am not an excel person at all - a coworker was terminated, and my boss has asked me to help her put calcuations into a spreadsheet that is due to accounting this afternoon. I was referred to this site as a possible solution. I am using Excel 07. =SUMPRODUCT(--(A2:A20="Open - Active")*(B2:B20<"Gvt Invest")*(C2:C120)) =SUMPRODUCT(--(A2:A20="Open - Active")*(B2:B20="Gvt Invest")*(C2:C120)) =SUMPRODUCT(--(A2:A20="Open - Probable")*(C2:C120)) See example and explanation below: STATUS NAT OF CASE RSV Open - Active EEOC 1000 Open - Probable EEOC 1000 Open - Active GVT Invest 1000 Open - Probable Gvt Invest 2000 How do I create formulas that will capture the subtotals totals for J for: Open - Active (Non - Government Investigations): Total all in spreadsheet that are Open - Active but not classified as Gvt invest. Total should equal 1000 Open - Active (Government Investigations): Total all in spreadsheet that are Open - Active but classified as Gvt invest. only. Total should equal 1000 Open - Probable: Totals all in spreadsheet that are Open - Probable. Total should equal 3000 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formulas Still Not Working
On Jan 6, 1:30*pm, Gord Dibben wrote:
"not working" means what? * Wrong answer? *Nothing? * Error? I tested my own and Isabelle's formulas on your sample data. Return all the values you ask for in your 3 scenarios 1000, * 1000, * 3000 In your first post you showed your data in columns F, G and H I based my formula on that sample. Where is your data? * Use a range when posting back. By range I mean * A2:C20 * *or F2:H10 * *or similar. We are assuming you have a header row for titles. Gord On Fri, 6 Jan 2012 11:08:35 -0800 (PST), Michelle wrote: I am so sorry to bother everyone, but would love it if someone could contunie to help me with these formulas. I made the initial inquiry below, and Isaeblle provided me with formulas (so appreciate the help), but they are not working in my spreadsheet. I am not an excel person at all - a coworker was terminated, and my boss has asked me to help her put calcuations into a spreadsheet that is due to accounting this afternoon. I was referred to this site as a possible solution. I am using Excel 07. =SUMPRODUCT(--(A2:A20="Open - Active")*(B2:B20<"Gvt Invest")*(C2:C120)) =SUMPRODUCT(--(A2:A20="Open - Active")*(B2:B20="Gvt Invest")*(C2:C120)) =SUMPRODUCT(--(A2:A20="Open - Probable")*(C2:C120)) See example and explanation below: STATUS * * * * * * * * * * *NAT OF CASE * * * * * * * * * RSV Open - Active * * * * * * * * * * * EEOC * * * * * * * * * * 1000 Open - Probable * * * * * * * * * EEOC * * * * * * * * * * 1000 Open - Active * * * * * * * * * * * GVT Invest * * * * * * *1000 Open - Probable * * * * * * * * * Gvt Invest * * * * * * * *2000 How do I create formulas that will capture the subtotals totals for J for: Open - Active *(Non - *Government Investigations): Total all in spreadsheet that are Open - Active but not classified as Gvt invest. Total should equal 1000 Open - Active (Government Investigations): Total all in spreadsheet that are Open - Active but classified as Gvt invest. only. Total should equal 1000 Open - Probable: Totals all in spreadsheet that are Open - Probable. Total should equal 3000- Hide quoted text - - Show quoted text - Thanks. The result I get is #VALUE. I do have header Rows. I ahve many more columns, but provided only the ones I thought were needed for the calculation. My rows (with data) start at 7 (Header is row 6) and contiune through 159 (so 152 total rows of data), but could grow beyond 156. My columns are A through P, so I guess that means my range is A7:P159 (but could be larger if more data were added? I want the result of the 3 formulas to populate in C1, C2 and C3 respectively, so that is where I entered the formula. A few things have been changed in the table and from the example I provided, but I corrected those in the formula. I have updated it below to be an accurate description, but I confirmed that the changes I hade to your formula comported. Sorry to be such a problem. Does this help? Im sure Im doing something wrong..... F G K STATUS STATUS RSV 7 Open - Active EEOC $1000 8 Open - Probable EEOC $1000 9 Open - Active Government Inquiry $1000 10 Open - Probable Government Inquiry $2000 So, in C1 (Open - Active (Non Government Inquiries): I put in the following formula: =SUMPRODUCT(--(F7:F1000="Open - Active")*(G7:G1000<"Government Inquiry")*(K7:K1000)) In C2 (Open - Active (Government Inquiries): I put =SUMPRODUCT(-- (F7:F1000="Open - Active")*(G7:G1000="Government Inquiry")*(K7:K1000)) In c3 (Open - Probable): I put =SUMPRODUCT(--(F7:F1000="Open - Probable")*(F7:F1000)) |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formulas Still Not Working
Hi Michelle,
Am Fri, 6 Jan 2012 12:02:46 -0800 (PST) schrieb Michelle: Thanks. The result I get is #VALUE. try: =SUMPRODUCT(--(A1:A20="Open - Active"),--(B1:B20="GVT Invest"),C1:C20) Regards Claus Busch -- Win XP PRof SP2 / Vista Ultimate SP2 Office 2003 SP2 /2007 Ultimate SP2 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formulas Still Not Working
On Jan 6, 2:06*pm, Claus Busch wrote:
Hi Michelle, Am Fri, 6 Jan 2012 12:02:46 -0800 (PST) schrieb Michelle: Thanks. The result I get is #VALUE. try: =SUMPRODUCT(--(A1:A20="Open - Active"),--(B1:B20="GVT Invest"),C1:C20) Regards Claus Busch -- Win XP PRof SP2 / Vista Ultimate SP2 Office 2003 SP2 /2007 Ultimate SP2 Claus - this worked! I placed the formula in all chree cells, and updated to capture the ranges I need, etc., and the calculations work correctly. Truly, from the bottom of my heart - I thank you ALL! I also think Im going to take an excel class! You are all amazing, and I wish I could thank you all in person! |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formulas Still Not Working
"Claus Busch" wrote:
Am Fri, 6 Jan 2012 12:02:46 -0800 (PST) schrieb Michelle: Thanks. The result I get is #VALUE. try: =SUMPRODUCT(--(A1:A20="Open - Active"),--(B1:B20="GVT Invest"),C1:C20) Or to simplify: =SUMPRODUCT((A1:A20="Open - Active")*(B1:B20="GVT Invest"),C1:C20) Things to note: 1. You do not need the double negative if you use multiplication in the arithemetic expression. The purpose of the double negative in this context is to convert TRUE and FALSE into 1 and 0. But any arithmetic operation will accomplish that. 2. Separate C1:C20 with a comma (separate parameter), instead of multiplying it in the arithmetic expression. The effect is the same. The difference is: SUMPRODUCT can recognize and ignore non-numeric data in C1:C20. When you multiply C1:C20, Excel's arithmetic expression evaluator does not tolerate non-numeric data. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formulas not working | Excel Worksheet Functions | |||
Formulas not working | Excel Worksheet Functions | |||
Formulas Not Working | Excel Discussion (Misc queries) | |||
Formulas Not Working | Excel Discussion (Misc queries) | |||
Formulas not working | Excel Worksheet Functions |