Re: SumIF by Pete_UK
Pete_UK
Fri Mar 14 18:31:16 CDT 2008
You have more than one criteria, so you can't use SUMIF - try this
instead:
=3DSUMPRODUCT((B$1:B$100>=3D0.2)*(B$1:B$100<0.3)*(A$1:A$100=3D"George"))
Or, if you put George in cell D1 (and other names below):
=3DSUMPRODUCT((B$1:B$100>=3D0.2)*(B$1:B$100<0.3)*(A$1:A$100=3DD1))
and copy down to cover your names in column D.
Hope this helps.
Pete
On Mar 14, 9:53=A0pm, RoadKill <RoadK...@discussions.microsoft.com>
wrote:
> Thinking it over, let's simplify it to a single workbook.
>
> When the contents of H1 (George) matches any instances of it in column
> A1:A100, I want it to count the number of scores of 20-29.99% in B1:B100.
>
>
>
> "RoadKill" wrote:
> > Okay, let me clarify. It sounds like this is close. If cell B1 in March
> > equals "George" then I want to count the number of Georges in Column A o=
f QTD
> > that have a score of 20-29.99% in Column B of QTD as well as the number =
of
> > scores above 30%.
>
> > Sorry for the confusion.
>
> > "Mike H" wrote:
>
> > > Hi,
>
> > > I think I understand so try this in march.xls
>
> > > =3DSUM(IF(([QTD.xls]Sheet1!$A$1:$A$100/B1>=3D0.2), 1, 0))
> > > Enter with Ctrl+Shift+Enter
>
> > > It will divide each cell in QTD.xls by march.xls b1 and count to numbe=
r of
> > > time the result is >=3D 20%. Change 0.2 to 0.3 for 30%
>
> > > Mike
>
> > > "RoadKill" wrote:
>
> > > > Okay, I am a total rookie with SumIFs. I want workbook March.xls to =
link
> > > > whatever is in cell B1 of March.xls to link to QTD.xls workbook colu=
mn
> > > > A1:A100 and count the number of instances that are over 20% as well =
as those
> > > > over 30%.
>
> > > > Does this make sense?
>
> > > > Thanks- Hide quoted text -
>
> - Show quoted text -