Need someone to examine this formula...

[soltek]

=SUM(IF((U8:U1001="D")*(L8:L1001>=J5)*(L8:L1001<=L5),I8:I1001))


Does this look like it should do its job correctly? If the first
column U equals "D", I want it to sum Column "I" if it is between J5
and L5. The equal to and less/greater is important. Im not sure if I
have stated this correctly.
I have created another worksheet to test these calculations more
dynamically, and it reports this formula is missing part of the grand
total. Im pretty sure it has something to do with the date refference.
Any help will be much appreciated.

 

[Want2Learn]

It looks like you are missing part of the IF statement.
You have the condition [((U8:U1001="D")*(L8:L1001>=J5)*
(L8:L1001<=L5)] and what happens if the condition is true
[I8:I1001))], but not what happens if the condition is
false. Don't know if this helps or not.

W2L

 

[Paul Corrado]

Try this Array formula (press Ctrl+Shift+Enter)

=SUM(IF(((U8:U1001="D")*(L8:L1001>=J5)*(L8:L1001<=L5))=TRUE,I8:I1001))

(I think I got that correct)

Or

This should also work

=SUMPRODUCT((U9:U1002="D")*(L9:L1002>=J6)*(L9:L1002<=L6)*(I9:I1002))

HTH

PC

 

[Frank Kabel]

Hi
the formula looks o.k. Just remember to enter this as array formula
(with CTRL+SHIFT+ENTER).

Also have a look at:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

 

[Frank Kabel]

Hi W2L
no need for the fALSE Condition. The formula returns simply FALSE in
these cases. And this result is ignored by the SUM function.

--
Regards
Frank Kabel
Frankfurt, Germany

It looks like you are missing part of the IF statement.
You have the condition [((U8:U1001="D")*(L8:L1001>=J5)*
(L8:L1001<=L5)] and what happens if the condition is true
[I8:I1001))], but not what happens if the condition is
false. Don't know if this helps or not.

W2L

-----Original Message-----

=SUM(IF((U8:U1001="D")*(L8:L1001>=J5)* (L8:L1001<=L5),I8:I1001))


Does this look like it should do its job correctly? If the first
column U equals "D", I want it to sum Column "I" if it is between J5
and L5. The equal to and less/greater is important. Im not sure if I
have stated this correctly.
I have created another worksheet to test these calculations more
dynamically, and it reports this formula is missing part of the grand
total. Im pretty sure it has something to do with the date refference.
Any help will be much appreciated.


--
soltek
---------------------------------------------------------- --------------
soltek's Profile: http://www.excelforum.com/member.php? action=getinfo&userid=13008
View this thread: http://www.excelforum.com/showthread.php?threadid=264260

.

 

[Ron Rosenfeld]

=SUM(IF((U8:U1001="D")*(L8:L1001>=J5)*(L8:L1001<=L5),I8:I1001))


Does this look like it should do its job correctly? If the first
column U equals "D", I want it to sum Column "I" if it is between J5
and L5. The equal to and less/greater is important. Im not sure if I
have stated this correctly.
I have created another worksheet to test these calculations more
dynamically, and it reports this formula is missing part of the grand
total. Im pretty sure it has something to do with the date refference.
Any help will be much appreciated.

Two possibilities come to mind.

1. This need to be entered as an *ARRAY* formula. To do so you hold down
<ctrl><shift> while hitting <enter>. If you did it correctly, excel will place
braces {...} around the formula.

2. The values in I8:I1001 are text and not numbers. To check this, enter in
some column =ISTEXT(I8) and drag/copy down. If this is TRUE, then you need to
convert the values to numbers. To do that:
select a blank, empty cell
Edit/Copy
select I8:I1001
Edit/Paste Special Operation Add


--ron