|
| If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|||||||
|
|
Thread Tools | Display Modes |
|
#1
January 24th, 2008, 08:02 AM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formulas in 2007 not working in 2003
I created a formula in Excel 2007:
=COUNTIFS(Workflow!G9:G305,"New Facility",Workflow!S9:S305,"Yes") and =SUMIFS(Workflow!U9:U305,Workflow!G9:G305,"New Facility",Workflow!S9:S305,"Yes",Workflow!U9:U305, "0") but it will appear that "COUNTIFS" and "SUMIFS" are not available in excel 2003. Do you perhaps know which alternatives i can use? -- Andrew 
|
|
#2
January 24th, 2008, 01:00 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Formulas in 2007 not working in 2003
COUNTIFS and SUMIFS are new to Excel 2007.
Try =SUMPRODUCT(--(Workflow!G9:G305="New Facility"),--(Workflow!S9:S305="Yes")) =SUMPRODUCT(--(Workflow!G9:G305="New Facility"),--(Workflow!S9:S305="Yes"), --(Workflow!U9:U3050),Workflow!U9:U305) -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Andrew" wrote in message news  I created a formula in Excel 2007: =COUNTIFS(Workflow!G9:G305,"New Facility",Workflow!S9:S305,"Yes") and =SUMIFS(Workflow!U9:U305,Workflow!G9:G305,"New Facility",Workflow!S9:S305,"Yes",Workflow!U9:U305, "0") but it will appear that "COUNTIFS" and "SUMIFS" are not available in excel 2003. Do you perhaps know which alternatives i can use? -- Andrew
|
|
#3
January 24th, 2008, 04:25 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Formulas in 2007 not working in 2003
Bob
Thanks for the feedback but it is ignoreing the second part off the rule =SUMPRODUCT(--(Workflow!G9:G305="New Facility"),--(Workflow!S9:S305="Yes")) I have 7 "New Facility" fields but only 2 with "Yes" The answer should be 2 -- Andrew "Bob Phillips" wrote: COUNTIFS and SUMIFS are new to Excel 2007. Try =SUMPRODUCT(--(Workflow!G9:G305="New Facility"),--(Workflow!S9:S305="Yes")) =SUMPRODUCT(--(Workflow!G9:G305="New Facility"),--(Workflow!S9:S305="Yes"), --(Workflow!U9:U3050),Workflow!U9:U305) -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Andrew" wrote in message news I created a formula in Excel 2007: =COUNTIFS(Workflow!G9:G305,"New Facility",Workflow!S9:S305,"Yes") and =SUMIFS(Workflow!U9:U305,Workflow!G9:G305,"New Facility",Workflow!S9:S305,"Yes",Workflow!U9:U305, "0") but it will appear that "COUNTIFS" and "SUMIFS" are not available in excel 2003. Do you perhaps know which alternatives i can use? -- Andrew
|
|
#4
January 24th, 2008, 07:33 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Formulas in 2007 not working in 2003
I bet you have hidden rows in your data and you're not seeing all 7 rows that
match. Andrew wrote: Bob Thanks for the feedback but it is ignoreing the second part off the rule =SUMPRODUCT(--(Workflow!G9:G305="New Facility"),--(Workflow!S9:S305="Yes")) I have 7 "New Facility" fields but only 2 with "Yes" The answer should be 2 -- Andrew "Bob Phillips" wrote: COUNTIFS and SUMIFS are new to Excel 2007. Try =SUMPRODUCT(--(Workflow!G9:G305="New Facility"),--(Workflow!S9:S305="Yes")) =SUMPRODUCT(--(Workflow!G9:G305="New Facility"),--(Workflow!S9:S305="Yes"), --(Workflow!U9:U3050),Workflow!U9:U305) -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Andrew" wrote in message news I created a formula in Excel 2007: =COUNTIFS(Workflow!G9:G305,"New Facility",Workflow!S9:S305,"Yes") and =SUMIFS(Workflow!U9:U305,Workflow!G9:G305,"New Facility",Workflow!S9:S305,"Yes",Workflow!U9:U305, "0") but it will appear that "COUNTIFS" and "SUMIFS" are not available in excel 2003. Do you perhaps know which alternatives i can use? -- Andrew -- Dave Peterson
|
|
#5
January 24th, 2008, 08:34 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Formulas in 2007 not working in 2003
Dave
All Rows open nothing hidden -- Andrew "Dave Peterson" wrote: I bet you have hidden rows in your data and you're not seeing all 7 rows that match. Andrew wrote: Bob Thanks for the feedback but it is ignoreing the second part off the rule =SUMPRODUCT(--(Workflow!G9:G305="New Facility"),--(Workflow!S9:S305="Yes")) I have 7 "New Facility" fields but only 2 with "Yes" The answer should be 2 -- Andrew "Bob Phillips" wrote: COUNTIFS and SUMIFS are new to Excel 2007. Try =SUMPRODUCT(--(Workflow!G9:G305="New Facility"),--(Workflow!S9:S305="Yes")) =SUMPRODUCT(--(Workflow!G9:G305="New Facility"),--(Workflow!S9:S305="Yes"), --(Workflow!U9:U3050),Workflow!U9:U305) -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Andrew" wrote in message news
I created a formula in Excel 2007:=COUNTIFS(Workflow!G9:G305,"New Facility",Workflow!S9:S305,"Yes") and =SUMIFS(Workflow!U9:U305,Workflow!G9:G305,"New Facility",Workflow!S9:S305,"Yes",Workflow!U9:U305, "0") but it will appear that "COUNTIFS" and "SUMIFS" are not available in excel 2003. Do you perhaps know which alternatives i can use? -- Andrew -- Dave Peterson
|
|
#6
January 24th, 2008, 11:44 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Formulas in 2007 not working in 2003
Do you have calculation set to automatic or manual?
(Tools|Options|calculation tab) If you have it set to automatic, I'd bet a dollar, you're missing a few when you count. How about adding a filter to that data. Filter to show "new Facility" in G9:G305 (add headers in row 8 if you need to) and filter to show "Yes" in s9:s305. You'll see 7 rows. If you have it set to manual, never mind that bet! Andrew wrote: Dave All Rows open nothing hidden -- Andrew "Dave Peterson" wrote: I bet you have hidden rows in your data and you're not seeing all 7 rows that match. Andrew wrote: Bob Thanks for the feedback but it is ignoreing the second part off the rule =SUMPRODUCT(--(Workflow!G9:G305="New Facility"),--(Workflow!S9:S305="Yes")) I have 7 "New Facility" fields but only 2 with "Yes" The answer should be 2 -- Andrew "Bob Phillips" wrote: COUNTIFS and SUMIFS are new to Excel 2007. Try =SUMPRODUCT(--(Workflow!G9:G305="New Facility"),--(Workflow!S9:S305="Yes")) =SUMPRODUCT(--(Workflow!G9:G305="New Facility"),--(Workflow!S9:S305="Yes"), --(Workflow!U9:U3050),Workflow!U9:U305) -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Andrew" wrote in message news
I created a formula in Excel 2007:=COUNTIFS(Workflow!G9:G305,"New Facility",Workflow!S9:S305,"Yes") and =SUMIFS(Workflow!U9:U305,Workflow!G9:G305,"New Facility",Workflow!S9:S305,"Yes",Workflow!U9:U305, "0") but it will appear that "COUNTIFS" and "SUMIFS" are not available in excel 2003. Do you perhaps know which alternatives i can use? -- Andrew -- Dave Peterson -- Dave Peterson
|
|
#7
January 25th, 2008, 04:44 AM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Formulas in 2007 not working in 2003
Dave
Yes calculation is set at automatic. When applying the filter I see 7 "New Facilities" if I filter on column G then selecting "Yes" on S only 2 remains, which is correct. The correct answer is 2 I need a total of all "New Facilities" with a "Yes" Row Column G Column S 9 New Facility Yes 10 Switch Yes 11 New Facility Yes 12 Increase Yes 13 New Facility 14 New Facility 15 New Facility 16 New Facility 17 New Facility I need a total of how many "New Facility" has a "Yes" In 2007 COUNTIFS is working perfectly -- Andrew "Dave Peterson" wrote: Do you have calculation set to automatic or manual? (Tools|Options|calculation tab) If you have it set to automatic, I'd bet a dollar, you're missing a few when you count. How about adding a filter to that data. Filter to show "new Facility" in G9:G305 (add headers in row 8 if you need to) and filter to show "Yes" in s9:s305. You'll see 7 rows. If you have it set to manual, never mind that bet! Andrew wrote: Dave All Rows open nothing hidden -- Andrew "Dave Peterson" wrote: I bet you have hidden rows in your data and you're not seeing all 7 rows that match. Andrew wrote: Bob Thanks for the feedback but it is ignoreing the second part off the rule =SUMPRODUCT(--(Workflow!G9:G305="New Facility"),--(Workflow!S9:S305="Yes")) I have 7 "New Facility" fields but only 2 with "Yes" The answer should be 2 -- Andrew "Bob Phillips" wrote: COUNTIFS and SUMIFS are new to Excel 2007. Try =SUMPRODUCT(--(Workflow!G9:G305="New Facility"),--(Workflow!S9:S305="Yes")) =SUMPRODUCT(--(Workflow!G9:G305="New Facility"),--(Workflow!S9:S305="Yes"), --(Workflow!U9:U3050),Workflow!U9:U305) -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Andrew" wrote in message news
I created a formula in Excel 2007:=COUNTIFS(Workflow!G9:G305,"New Facility",Workflow!S9:S305,"Yes") and =SUMIFS(Workflow!U9:U305,Workflow!G9:G305,"New Facility",Workflow!S9:S305,"Yes",Workflow!U9:U305, "0") but it will appear that "COUNTIFS" and "SUMIFS" are not available in excel 2003. Do you perhaps know which alternatives i can use? -- Andrew -- Dave Peterson -- Dave Peterson
|
|
#8
January 25th, 2008, 07:10 AM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Formulas in 2007 not working in 2003
Thanks for the feedback but it is ignoreing
the second part off the rule =SUMPRODUCT(--(Workflow!G9:G305="New Facility"),--(Workflow!S9:S305="Yes")) I have 7 "New Facility" fields but only 2 with "Yes" The answer should be 2 What result do you get with the above formula? In 2007 COUNTIFS is working perfectly Hmmm... Try one of these: =SUMPRODUCT(--(TRIM(Workflow!G9:G305)="New Facility"),--(TRIM(Workflow!S9:S305)="Yes")) =SUMPRODUCT(--(Workflow!G9:G305&Workflow!S9:S305="New FacilityYes")) This array formula** : =SUM((Workflow!G9:G305="New Facility")*(Workflow!S9:S305="Yes")) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "Andrew" wrote in message ... Dave Yes calculation is set at automatic. When applying the filter I see 7 "New Facilities" if I filter on column G then selecting "Yes" on S only 2 remains, which is correct. The correct answer is 2 I need a total of all "New Facilities" with a "Yes" Row Column G Column S 9 New Facility Yes 10 Switch Yes 11 New Facility Yes 12 Increase Yes 13 New Facility 14 New Facility 15 New Facility 16 New Facility 17 New Facility I need a total of how many "New Facility" has a "Yes" In 2007 COUNTIFS is working perfectly -- Andrew "Dave Peterson" wrote: Do you have calculation set to automatic or manual? (Tools|Options|calculation tab) If you have it set to automatic, I'd bet a dollar, you're missing a few when you count. How about adding a filter to that data. Filter to show "new Facility" in G9:G305 (add headers in row 8 if you need to) and filter to show "Yes" in s9:s305. You'll see 7 rows. If you have it set to manual, never mind that bet! Andrew wrote: Dave All Rows open nothing hidden -- Andrew "Dave Peterson" wrote: I bet you have hidden rows in your data and you're not seeing all 7 rows that match. Andrew wrote: Bob Thanks for the feedback but it is ignoreing the second part off the rule =SUMPRODUCT(--(Workflow!G9:G305="New Facility"),--(Workflow!S9:S305="Yes")) I have 7 "New Facility" fields but only 2 with "Yes" The answer should be 2 -- Andrew "Bob Phillips" wrote: COUNTIFS and SUMIFS are new to Excel 2007. Try =SUMPRODUCT(--(Workflow!G9:G305="New Facility"),--(Workflow!S9:S305="Yes")) =SUMPRODUCT(--(Workflow!G9:G305="New Facility"),--(Workflow!S9:S305="Yes"), --(Workflow!U9:U3050),Workflow!U9:U305) -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Andrew" wrote in message news
I created a formula in Excel 2007:=COUNTIFS(Workflow!G9:G305,"New Facility",Workflow!S9:S305,"Yes") and =SUMIFS(Workflow!U9:U305,Workflow!G9:G305,"New Facility",Workflow!S9:S305,"Yes",Workflow!U9:U305, "0") but it will appear that "COUNTIFS" and "SUMIFS" are not available in excel 2003. Do you perhaps know which alternatives i can use? -- Andrew -- Dave Peterson -- Dave Peterson
|
|
#9
January 25th, 2008, 12:26 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Formulas in 2007 not working in 2003
On Thu, 24 Jan 2008 08:25:01 -0800, Andrew
wrote: Bob Thanks for the feedback but it is ignoreing the second part off the rule =SUMPRODUCT(--(Workflow!G9:G305="New Facility"),--(Workflow!S9:S305="Yes")) I have 7 "New Facility" fields but only 2 with "Yes" The answer should be 2 -- Andrew What result do you get? If you are getting a zero, then what you see as "Yes" does not contain "Yes". It probably also contains a space or a nbsp. --ron
|
|
#10
January 25th, 2008, 05:44 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Formulas in 2007 not working in 2003
"Ron Rosenfeld" wrote in message
... On Thu, 24 Jan 2008 08:25:01 -0800, Andrew wrote: Bob Thanks for the feedback but it is ignoreing the second part off the rule =SUMPRODUCT(--(Workflow!G9:G305="New Facility"),--(Workflow!S9:S305="Yes")) I have 7 "New Facility" fields but only 2 with "Yes" The answer should be 2 -- Andrew What result do you get? If you are getting a zero, then what you see as "Yes" does not contain "Yes". It probably also contains a space or a nbsp. --ron But this doesn't make sense: In 2007 COUNTIFS is working perfectly -- Biff Microsoft Excel MVP
|
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
