Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
SUMIF/SUMPRODUCT/IF - 2 criteria - Date and Text
After reading a few websites I find that SUMIF won't cut it for multiple
critera. However it appears SUMPRODUCT may do it, but unsure how I achieve what I need with SUMPRODUCT. Below is the actual table I have in my excel sheet. I would like to sum the column RENT/MTH (which is a calculated field RENT/WK * 4) if the TYPE = NEW and also if the START DATE is <= to a date in another field (lets us say in field B6). Pay/Wk Pay/Mth Rent/Wk Rent/Mth Start Date Type $200.00 $866.66 $106.00 $459.33 31/08/2005 NEW $210.00 $909.99 $138.00 $598.00 31/08/2005 OLD $200.00 $866.66 $167.00 $723.66 31/08/2005 BEST Can someone help with development of the formula? Thanks in advance. James |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
SUMIF/SUMPRODUCT/IF - 2 criteria - Date and Text
Hi
=SUMPRODUCT(--(Type="NEW"),--(StartDate<=$B$6),RentMth) -- Arvi Laanemets ( My real mail address: arvi.laanemets<attarkon.ee ) "James T" wrote in message ... After reading a few websites I find that SUMIF won't cut it for multiple critera. However it appears SUMPRODUCT may do it, but unsure how I achieve what I need with SUMPRODUCT. Below is the actual table I have in my excel sheet. I would like to sum the column RENT/MTH (which is a calculated field RENT/WK * 4) if the TYPE = NEW and also if the START DATE is <= to a date in another field (lets us say in field B6). Pay/Wk Pay/Mth Rent/Wk Rent/Mth Start Date Type $200.00 $866.66 $106.00 $459.33 31/08/2005 NEW $210.00 $909.99 $138.00 $598.00 31/08/2005 OLD $200.00 $866.66 $167.00 $723.66 31/08/2005 BEST Can someone help with development of the formula? Thanks in advance. James |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
SUMIF/SUMPRODUCT/IF - 2 criteria - Date and Text
Hi, I have a similar doubt, but I couldn't understand the solution. Let’s take the same example: A B C 1 Rent/Mth Start Date Type 2 $459.33 31/08/2005 NEW 3 $598.00 31/08/2005 OLD 6 $723.66 31/08/2005 BEST 5 .... 6 .... dd/mm/yyyy So, to get a sum of column B when cell of column C="NEW" and when cell of column B<B6, I tried to use the formula: =SUMIF(C:C,C1,SUMIF(B:B,B6,A:A)) Unfortunately, it doesn't work. How can I get this sum without an Auxiliary Column? Tks, -- Sabine ------------------------------------------------------------------------ Sabine's Profile: http://www.excelforum.com/member.php...o&userid=34800 View this thread: http://www.excelforum.com/showthread...hreadid=545381 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
SUMIF/SUMPRODUCT/IF - 2 criteria - Date and Text
Hi
=SUMPRODUCT(--(C2:C10000="NEW"),--(B2:B10000<=$B$6),A2:A10000) (adjust ranges to real ones) NB! SUMPRODUCT doesn't support whole-column references. And multiplied ranges must be of same dimension (same number of rows/columns). How this works. For every element group, all elements are multiplied, and results are summed. With your data, are calculated values (--(C2="NEW"))*(--(B2<=B6))*A2 (--(C3="NEW"))*(--(B3<=B6))*A3 (--(C4="NEW"))*(--(B4<=B6))*A4 .... Now, both equations in formula can be true or false. An unary transformation (--) converts TRUE/FALSE to their numeric equivalent, which is 0 for False and 1 for TRUE So summed are results of calculations like 0*0*Value 1*0*Value 0*1*Value 1*1*Value I.e. summed are values from rows, where both conditions were TRUE Arvi Laanemets "Sabine" wrote in message ... Hi, I have a similar doubt, but I couldn't understand the solution. Let's take the same example: A B C 1 Rent/Mth Start Date Type 2 $459.33 31/08/2005 NEW 3 $598.00 31/08/2005 OLD 6 $723.66 31/08/2005 BEST 5 .... 6 .... dd/mm/yyyy So, to get a sum of column B when cell of column C="NEW" and when cell of column B<B6, I tried to use the formula: =SUMIF(C:C,C1,SUMIF(B:B,B6,A:A)) Unfortunately, it doesn't work. How can I get this sum without an Auxiliary Column? Tks, -- Sabine ------------------------------------------------------------------------ Sabine's Profile: http://www.excelforum.com/member.php...o&userid=34800 View this thread: http://www.excelforum.com/showthread...hreadid=545381 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
SUMIF/SUMPRODUCT/IF - 2 criteria - Date and Text
It works !!!! Tks a lot :-) -- Sabine ------------------------------------------------------------------------ Sabine's Profile: http://www.excelforum.com/member.php...o&userid=34800 View this thread: http://www.excelforum.com/showthread...hreadid=545381 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
COUNTIF: 2 criteria: Date Range Column & Text Column | Excel Worksheet Functions | |||
Count items when specific text and date criteria are met | Excel Worksheet Functions | |||
Excel enters date as a text format | Excel Discussion (Misc queries) | |||
How do I convert a number formated as a date to text in Excel? | Excel Discussion (Misc queries) | |||
Advanced Filter using Date represented as text | Excel Worksheet Functions |