Sumif Function

[Jeff]

Hello,

I need help on a Sumif Function:
I need to Sumif the values in column F if and only if column B has "6" and
column C has "F"
Here's an example:

A B C D E F
54 6 M Q SptAcc 45
75 6 M E Escada 13
1 6 F 1 MEscada 761
9 6 F E Escada 20,091
9 6 F H Accessory 335

--
Regards,
Jeff

Hi

Try this:
=SUMPRODUCT(--(B2:B100=6),--(C2:C100="F"),--(F2:F100))

Andy.

"Jeff" <[email protected]> wrote in message
news:[email protected]...
> Hello,
>
> I need help on a Sumif Function:
> I need to Sumif the values in column F if and only if column B has "6" and
> column C has "F"
> Here's an example:
>
> A B C D E F
> 54 6 M Q SptAcc 45
> 75 6 M E Escada 13
> 1 6 F 1 MEscada 761
> 9 6 F E Escada 20,091
> 9 6 F H Accessory 335
>
> --
> Regards,
> Jeff
>

[Dave Peterson]

=sumproduct(--(b1:b999=6),--(c1:c999="f"),(f1:f999))

Adjust the ranges to match--but you can't use whole columns.

=sumproduct() likes to work with numbers. The -- stuff changes trues and falses
to 1's and 0's.

Bob Phillips explains =sumproduct() in much more detail here:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

And J.E. McGimpsey has some notes at:
http://mcgimpsey.com/excel/formulae/doubleneg.html

Jeff wrote:
>
> Hello,
>
> I need help on a Sumif Function:
> I need to Sumif the values in column F if and only if column B has "6" and
> column C has "F"
> Here's an example:
>
> A B C D E F
> 54 6 M Q SptAcc 45
> 75 6 M E Escada 13
> 1 6 F 1 MEscada 761
> 9 6 F E Escada 20,091
> 9 6 F H Accessory 335
>
> --
> Regards,
> Jeff

--

Dave Peterson

[Portuga]

Hello,

I need help on a Sumif Function:
I need to Sumif the values in column F if and only if column B has "6" and
column C has "F"
Here's an example:

A B C D E F
54 6 M Q SptAcc 45
75 6 M E Escada 13
1 6 F 1 MEscada 761
9 6 F E Escada 20,091
9 6 F H Accessory 335

--
Regards,
Jeff

Considering your sample starts on A1:

Put on cel G1 the following formula:
=IF(B1=6,F1,0)

Column G will now display values only when column B is 6. You can use column G to get your totals.

[ampm]

Hello,

I have a question. Same situation as Jeff's but what if one of column
F (say... 761) returns a #VALUES! because it's in the form of a formula and
still waiting for a value. How will you Sumif the values?

Thanks in advance,
ampm


"Andy" wrote:

> Hi
>
> Try this:
> =SUMPRODUCT(--(B2:B100=6),--(C2:C100="F"),--(F2:F100))
>
> Andy.
>
> "Jeff" <[email protected]> wrote in message
> news:[email protected]...
> > Hello,
> >
> > I need help on a Sumif Function:
> > I need to Sumif the values in column F if and only if column B has "6" and
> > column C has "F"
> > Here's an example:
> >
> > A B C D E F
> > 54 6 M Q SptAcc 45
> > 75 6 M E Escada 13
> > 1 6 F 1 MEscada 761
> > 9 6 F E Escada 20,091
> > 9 6 F H Accessory 335
> >
> > --
> > Regards,
> > Jeff
> >
>
>
>

[Dave Peterson]

I'd use something like:
=SUM((B1:B999=6)*(C1:C999="f")*(IF(ISNUMBER(F1:F999),F1:F999)))

This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
correctly, excel will wrap curly brackets {} around your formula. (don't type
them yourself.)

Adjust the range to match--but you still can't use the whole column.

ampm wrote:
>
> Hello,
>
> I have a question. Same situation as Jeff's but what if one of column
> F (say... 761) returns a #VALUES! because it's in the form of a formula and
> still waiting for a value. How will you Sumif the values?
>
> Thanks in advance,
> ampm
>
> "Andy" wrote:
>
> > Hi
> >
> > Try this:
> > =SUMPRODUCT(--(B2:B100=6),--(C2:C100="F"),--(F2:F100))
> >
> > Andy.
> >
> > "Jeff" <[email protected]> wrote in message
> > news:[email protected]...
> > > Hello,
> > >
> > > I need help on a Sumif Function:
> > > I need to Sumif the values in column F if and only if column B has "6" and
> > > column C has "F"
> > > Here's an example:
> > >
> > > A B C D E F
> > > 54 6 M Q SptAcc 45
> > > 75 6 M E Escada 13
> > > 1 6 F 1 MEscada 761
> > > 9 6 F E Escada 20,091
> > > 9 6 F H Accessory 335
> > >
> > > --
> > > Regards,
> > > Jeff
> > >
> >
> >
> >

--

Dave Peterson

[[email protected]]

Far as I know, sumif can only check 1 column in its first range...so
what I do is create a new column, where you concatenate your column B &
C...then use that concatenated column as the first range of the sumif

new column G
=B&C

copied down for each row results in:

G
6M
6M
6F
6F
6F

Then use this formula (assuming data starts in row 2)

sumif(G2:G6,"6F",F2:F6)

Based on your data, you get a result of 21,187


Hope this helps

Alan


Jeff wrote:
> Hello,
>
> I need help on a Sumif Function:
> I need to Sumif the values in column F if and only if column B has "6" and
> column C has "F"
> Here's an example:
>
> A B C D E F
> 54 6 M Q SptAcc 45
> 75 6 M E Escada 13
> 1 6 F 1 MEscada 761
> 9 6 F E Escada 20,091
> 9 6 F H Accessory 335
>
> --
> Regards,
> Jeff