Re: Sumif() with criteria

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

From: Jim May (jmay_at_cox.net)
Date: 02/18/05 

Date: Fri, 18 Feb 2005 07:43:29 -0500

Got it -- never mind

{=SUM((MyDirects!$D$7:$D$82=TestFormula!C10)*(MyDirects!$M$7:$M$82="Y")*MyDi
rects!$L$7:$L$82)}

"Max" <demechanik@yahoo.com> wrote in message
news:umsmosVFFHA.2824@tk2msftngp13.phx.gbl...
> One way
>
> In Sheet2
>
> Assuming A2 contains: 450300
>
> you could put in B2:
>
>
=SUMPRODUCT((Sheet1!$G$1:$G$100=A2)*(Sheet1!$K$1:$K$100="Y"),Sheet1!$J$1:$J$
> 100)
>
> B2 can be copied down for other values in A3, A4 ..
>
> Adapt the ranges to suit ..
> (but you can't use entire col refs in SUMPRODUCT)
>
> --
> Rgds
> Max
> xl 97
> ---
> GMT+8, 1° 22' N 103° 45' E
> xdemechanik <at>yahoo<dot>com
> ----
> "Jim May" <jmay@cox.net> wrote in message
> news:nZaRd.42327$EG1.27480@lakeread04...
> > Today I had need to bring into my Sheet2 some data off of Sheet1;
> >
> > My Sheet1 data (Column and Row#'s provided)
> > G H J K
> > 12 450300 Widget1 1,234.00 Y
> > 13 500200 Widget2 2,111.00 Y
> > 14 450300 Widget3 3,111.00 N
> > 15 650200 Widget4 4,111.00 Y
> > 15 450300 Widget5 5,111.00 Y
> > 15 353700 Widget6 6,111.00 Y
> > 16 450300 Widget7 2,333.00 N
> >
> > On mY *** 2 I need to Bring bank the sum of ColJ of all
> > records where ColG = 450300 and ColK = Y
> >
> > the answer would be 6,345.00
> >
> > What would formula be?
> >
> > Can/Should I use a:
> > Sumif()
> > Sumproduct()
> > An Array-entered formula
> >
> > TIA,
> >
> >
> >
>
>

Quantcast