Sumif Function

[Guest]

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

 

[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

 

[Guest]

Hi

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

Andy.

 

[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

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.

 

[Guest]

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

Hi

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

Andy.

 

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

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

 

[alan]

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