Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sumif Function
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sumif Function
=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 he 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sumif Function
Hi
Try this: =SUMPRODUCT(--(B2:B100=6),--(C2:C100="F"),--(F2:F100)) Andy. "Jeff" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sumif Function
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 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. -- Portuga ------------------------------------------------------------------------ Portuga's Profile: http://www.excelforum.com/member.php...fo&userid=6385 View this thread: http://www.excelforum.com/showthread...hreadid=547083 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sumif Function
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" wrote in message ... 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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sumif Function
I'd use something like:
=SUM((B1:B999=6)*(C1:C999="f")*(IF(ISNUMBER(F1:F99 9),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" wrote in message ... 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 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sumif Function
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can I add and IF function to a SUMIF function? | Excel Worksheet Functions | |||
SUMIF Function Inside SUMPRODUCT Function | Excel Worksheet Functions | |||
SumIF function | Excel Discussion (Misc queries) | |||
Sumif function with remote cell references | Excel Worksheet Functions | |||
SUMIF function | Excel Worksheet Functions |