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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 | |
|
|