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

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

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

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

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