Prev: how do I drag and fill hyperlinks?
Next: Formula Excel
From: Courtney on 19 Apr 2010 16:27
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
From: T. Valko on 19 Apr 2010 16:54
>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" <cprince(a)fficNOSPAM.com> wrote in message
news:d642c5f5ecd941329fa755e81e2806c4(a)newspe.com...
> 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


From: Courtney on 19 Apr 2010 19:24
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
From: T. Valko on 19 Apr 2010 22:01
>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" <cprince(a)fficNOSPAM.com> wrote in message
news:a80e6bd35df341b69b48fb41633d10e0(a)newspe.com...
> 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


 | 
Pages: 1
Prev: how do I drag and fill hyperlinks?
Next: Formula Excel