Excel 2003 formula

  • Thread starter Thread starter Rick
  • Start date Start date
R

Rick

I have a formula =IF(L5="YES",SUM(I5),"")

I want to be able to look at all the cells in column L
for "YES" and if found, SUM all the corresponding cells
in I. That is, if there are 5 cells in column L and 2 of
them (L2 and L4)have "YES", I want to sum I2 and I4 in
column U2.

Thanks in advance for the help.
Rick
 
try =sumproduct(INT(L2:L6="YES"),I2:I6).

This assumes that your data is in cells L2 to L6, I2 to I
respectively
 
Rick said:
I have a formula =IF(L5="YES",SUM(I5),"")
Click to expand...

=SUMIF(L5,"YES",I5)

would be more compact.
I want to be able to look at all the cells in column L
for "YES" and if found, SUM all the corresponding cells
in I. That is, if there are 5 cells in column L and 2 of
them (L2 and L4)have "YES", I want to sum I2 and I4 in
column U2.
Click to expand...

Maybe =SUMIF(L2:L6,"YES",I2:I6)
 
The formula you supplied works. However, I need to
include all cells in column L and column I (I will be
using this spreadsheet over the course of the next 12
months so it will grow considerably). I have tried to
use L:L and I:I, but get an error. Any suggestions?

Also, I failed to mention that I need to limit the result
further with the inclusion of column "A" - the date the
entry was made. So, for demonstration purposes, let's say
I have the following:

A L I U
1/6/2004 YES 60.00 180.00
1/18/2004 YES 120.00
1/22/2004 500.00
2/9/2004 YES 200.00
2/15/2004 75.00
3/22/200 YES 690.00

In the above example, U2 would equal 180.00 because I2
and I3 were entered in January, there is a "Yes" in L2
and L3, and I2 and I3 equal 180.00

Thank you for your help.
Rick
 
For your original question, Harlan has given you the answer you should use.
Kieran's answer cannot be used with full column references as it is an array
formula, however, given your last post, then it is probably SUMPRODUCT you will
need to do this, because that can take more than one criteria whereas SUMIF
can't (Which Harlan would also have told you had he been aware of the full
story).

Given your text then assuming your month was in a single cell as a number, eg 1
for Jan in say F2, then the following will give you what you need for each month
simply by changing the number in F2

=SUMPRODUCT((MONTH(A2:A10)=$F$2)*(B2:B10="YES")*(C2:C10))

If however, as per your example, you want to put a formula down col U and have
it calculate this data for whatever is in the corresponding cell in Col A, then
you need to lock the references, and make them of a size sufficient to cover all
your potential entries. You also need to reference the value in Col A instead
of a single cell in F2 eg:-

=SUMPRODUCT((MONTH($A$2:$A$10000)=MONTH($A2))*($B$2:$B$10000="YES")*($C$2:$C$100
00))

You can even make the ranges dynamic by using the OFFSET function if you like.

Personally though, despite all the above, I would probably use a Pivot table for
what you are trying to do - Much much easier and more flexible in terms of
reporting. Also, if you name the range 'Database' and then add records to it
using the form from Data / Form, the range will expand automatically to include
new records.

If you want an intro to Pivot tables, then try Debra Dalgleish's site:-

http://www.geocities.com/jonpeltier/Excel/Pivots/pivotstart.htm
 
Hi

Define named ranges (I assume your table is on sheet 'Sheet1', and in row 1
are column headings)
Dates=OFFSET(Sheet1!$A$2,,,COUNTIF(Sheet1!$A:$A,"<>")-1,1)
YesNo=OFFSET(Sheet1!$L$2,,,COUNTIF(Sheet1!$A:$A,"<>")-1,1)
Values=OFFSET(Sheet1!$I$2,,,COUNTIF(Sheet1!$A:$A,"<>")-1,1)

The formula will be
=SUMPRODUCT((MONTH(Dates)=1)*(YesNo="Yes")*(Values))
to sum all Values in January with have 'YES' in column L
Or
=IF(A2="","",SUMPRODUCT((MONTH(Dates)=MONTH(A2))*(YesNo="Yes")*(Values)))
(and copy down)
to display for every entry the sum of Values in entrie's month with have
'YES' in column L - nothing is displayed when date field is empty.
 
Back
Top