Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 2
Default COUNTIFS equivalent in Excel 2003 - both criteria have text, one has "wildcard"

Hi all,

I am trying to find a way to write the following formula in Excel 2003. I
have tried SUMPRODUCT but it does not seem to work when both criteria have
text values. I tried COUNTIF & COUNTIF but I think the wildcard * is
throwing off the result.

=COUNTIFS('Cycle 1'!$B$14:$B$1069, "Auto*", 'Cycle 1'!$J$14:$J$1069, "100%")


Please help!
Thank you!

url:http://www.ureader.com/gp/1042-1.aspx
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 15,768
Default COUNTIFS equivalent in Excel 2003 - both criteria have text, one has "wildcard"

COUNTIFS('Cycle 1'!$B$14:$B$1069, "Auto*", 'Cycle 1'!$J$14:$J$1069, "100%")

Does the range J14:J1069 contain numbers formatted as Percentage?

See if this does what you want...

=SUMPRODUCT(--(LEFT('Cycle 1'!$B$14:$B$1069,4)="Auto"),--('Cycle
1'!$J$14:$J$1069=1))

--
Biff
Microsoft Excel MVP


"Courtney" wrote in message
.. .
Hi all,

I am trying to find a way to write the following formula in Excel 2003. I
have tried SUMPRODUCT but it does not seem to work when both criteria have
text values. I tried COUNTIF & COUNTIF but I think the wildcard * is
throwing off the result.

=COUNTIFS('Cycle 1'!$B$14:$B$1069, "Auto*", 'Cycle 1'!$J$14:$J$1069,
"100%")


Please help!
Thank you!

url:http://www.ureader.com/gp/1042-1.aspx



  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 2
Default COUNTIFS equivalent in Excel 2003 - both criteria have text, one has "wildcard"

Hi thanks for your quick response. I'm getting a "#DIV/0" error. Soemthing
must be wrong because when I use COUNTIFS in 2007, the result is 26. Any
ideas of what could be causing this error or what could fix the formula?

url:http://www.ureader.com/msg/104258258.aspx
  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 15,768
Default COUNTIFS equivalent in Excel 2003 - both criteria have text, one has "wildcard"

I'm getting a "#DIV/0" error.

Do you have #DIV/0! errors in any of the referenced ranges?

Try this array formula** :

=SUM(IF(LEFT('Cycle 1'!B14:B1069,4)="Auto",IF(ISNUMBER('Cycle
1'!J14:J1069),IF('Cycle 1'!J14:J1069=1,1))))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

--
Biff
Microsoft Excel MVP


"Courtney" wrote in message
.. .
Hi thanks for your quick response. I'm getting a "#DIV/0" error.
Soemthing
must be wrong because when I use COUNTIFS in 2007, the result is 26. Any
ideas of what could be causing this error or what could fix the formula?

url:http://www.ureader.com/msg/104258258.aspx



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
formula similar to "countifs" function in excel 2007 embee Excel Worksheet Functions 2 April 19th 10 05:19 PM
Excel Equivalent of Access "Load" Event? LarryP Excel Discussion (Misc queries) 2 April 7th 10 08:44 PM
"Find" a wildcard as a place marker and "replace" with original va Eric Excel Discussion (Misc queries) 1 January 27th 09 07:00 PM
Need function that will work in Excel 2003 like "Countifs" in 2007 RD[_2_] Excel Worksheet Functions 3 August 1st 08 04:35 PM
Is there an Excel 2003 equivalent to Word's "versions" function? Steve Excel Discussion (Misc queries) 0 March 4th 07 03:01 AM


All times are GMT +1. The time now is 02:43 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"