Formulas in 2007 not working in 2003

[Andrew]

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?

 

[Bob Phillips]

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:U305>0),Workflow!U9:U305)

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Andrew]

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

 

[Dave Peterson]

I bet you have hidden rows in your data and you're not seeing all 7 rows that
match.

 

[Andrew]

Dave

All Rows open nothing hidden
--
Andrew

I bet you have hidden rows in your data and you're not seeing all 7 rows that
match.

 

[Dave Peterson]

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]

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

 

[T. Valko]

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

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

 

[Ron Rosenfeld]

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

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

 

[T. Valko]

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:

 

[Ron Rosenfeld]

But this doesn't make sense:

That assumes that he opened the exact same workbook in 2007 and then in 2003.
If that is the case, then I would agree with you about it not making sense
because the data should be the same.

But he did not indicate that he had done that.
--ron

 

[Harlan Grove]

=SUMPRODUCT(--(Workflow!G9:G305="New Facility"),
--(Workflow!S9:S305="Yes"))


What result do you get with the above formula?
....
=SUMPRODUCT(--(TRIM(Workflow!G9:G305)="New Facility"),
--(TRIM(Workflow!S9:S305)="Yes"))

=SUMPRODUCT(--(Workflow!G9:G305&Workflow!S9:S305="New FacilityYes"))
....
=SUM((Workflow!G9:G305="New Facility")*(Workflow!S9:S305="Yes"))

But if COUNTIFS and these last 3 formulas work but the first formula
above doesn't, that would imply COUNTIFS ignores trailing blanks.
Since you now have Excel 2007, you want to test that hypothesis?

I'd also note that the OP hasn't mentioned whether the SUMPRODUCT
formulas are failing (returning 7 rather than 2) under Excel 2007,
Excel 2003 or both.

 

[T. Valko]

But if COUNTIFS and these last 3 formulas work but the first formula
above doesn't, that would imply COUNTIFS ignores trailing blanks.
Since you now have Excel 2007, you want to test that hypothesis?

COUNTIFS does not ignore leading/trailing white space chars 32 or 160.

Once I get squared away I'll be interested to see efficiency comparisons
between COUNTIFS / SUMIFS and the equivalent SUMPRODUCT.

The more I use Excel 2007, the more I'm disliking the ribbon.

 

[Bob Phillips]

COUNTIFS/SUMIFS out-perform SUMPRODUCT, no contest.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Bob Phillips]

It has to be your data, I have just re-created your example, and I get 2.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)

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]

Ron

I created the file on excel 2003 then transfered it to my desktop with excel
2007 because i could not find the a formula to work in 2003. After finding a
formula on 2007 i transfered the file back to my laptop.

I also created a new file today in 2003 to see if I can use one off the
abovementioned suggestions but i still have problems. I either get a wrong
answer or #Value.

Also made sure that all my add on's was activated.

I am starting to think i have a problem with one of my excel setting on the
laptop but as far as i know all appear to be fine

 

[Ron Rosenfeld]

Ron

I created the file on excel 2003 then transfered it to my desktop with excel
2007 because i could not find the a formula to work in 2003. After finding a
formula on 2007 i transfered the file back to my laptop.

I also created a new file today in 2003 to see if I can use one off the
abovementioned suggestions but i still have problems. I either get a wrong
answer or #Value.

Also made sure that all my add on's was activated.

I am starting to think i have a problem with one of my excel setting on the
laptop but as far as i know all appear to be fine

Andrew,

What result is returned using the SUMPRODUCT formula in 2003?
--ron

 

[Ron Rosenfeld]

Ron

I created the file on excel 2003 then transfered it to my desktop with excel
2007 because i could not find the a formula to work in 2003. After finding a
formula on 2007 i transfered the file back to my laptop.

I also created a new file today in 2003 to see if I can use one off the
abovementioned suggestions but i still have problems. I either get a wrong
answer or #Value.

Also made sure that all my add on's was activated.

I am starting to think i have a problem with one of my excel setting on the
laptop but as far as i know all appear to be fine

Actually, I should have asked about the result of the SUMPRODUCT formula in
both 2007 and 2003.

ie

Results

SUMIFS (2007):

SUMPRODUCT (in the same 2007 workbook as the SUMIFS):

SUMPRODUCT (2003):

When you created the new file in 2003, how did you get the data from 2007 to
2003? Did you copy a sheet, or did you access it via a web interface?
--ron

 

[Andrew]

Ron

It is calculating 7 -- the number of "New Facility" in G

 

[Ron Rosenfeld]

Ron

It is calculating 7 -- the number of "New Facility" in G

In your 2003 workbook

Select Tools/Options/Transition/Workbook Options and DEselect Transition
Formula Evaluation. (You should probably also DEselect Transition Formula
Entry if it is selected).
--ron