A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

COUNTIFS equivalent in Excel 2003 - both criteria have text, one has "wildcard"



 
 
Thread Tools Display Modes
  #1  
Old April 19th, 2010, 09:27 PM posted to microsoft.public.excel.worksheet.functions
Courtney[_3_]
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  
Old April 19th, 2010, 09:54 PM posted to microsoft.public.excel.worksheet.functions
T. Valko
external usenet poster
 
Posts: 15,759
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  
Old April 20th, 2010, 12:24 AM posted to microsoft.public.excel.worksheet.functions
Courtney[_3_]
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  
Old April 20th, 2010, 03:01 AM posted to microsoft.public.excel.worksheet.functions
T. Valko
external usenet poster
 
Posts: 15,759
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



 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 06:00 PM.


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