Sum column based on value in each row, if two cells equal, or if date is within time period

[clynneo]

I need help - I actually am trying to do two separate functions, but I
think the formula should be similar for both.

#1: I am trying to sum a column if two cells in each row are equal.
For example:


Date N. Hours D. Hours Total
Row1 1/1/2006 3 1 4
Row2 4/4/2006 2 2 2
Row3 8/19/2006 1 1
Row4 9/19/2009 4 4 4


I want to sum the "total" column for all rows that have "n hours" and
"d hours" equal.


#2: Using the same data example, I want to sum the "total" column if
the value in the "date" column is within the past 90 days.


Can anyone help? I would be ever so grateful!


TIA -
Cynthia

 

[Carim]

Hi,

With an array formula ...
{=SUM(IF((B1:B4)=(C1:C4),D14))}

Once you have typed your formula, use Ctrl Shift Enter in order to
enter formula

HTH
Cheers
Carim

 

[Carim]

Hi,

Forgot the second formula ..

{=SUM(IF((A1:A4)>=TODAY()+90,D14))}

Cheers
Carim

 

[clynneo]

Thanks so much Carim. Here's how the formulas ended up looking, but I
still get the #NUM! as an error. Any ideas? Does it matter that I'm
referencing another worksheet?

=SUM(IF(('Flight Log'!O:O)=('Flight Log'!Q:Q),'Flight Log'!R:R))

=SUM(IF(('Flight Log'!A:A)>=TODAY()-90,'Flight Log'!R:R))

 

[Carim]

Hi,

You can refer to another worksheet, but you have to define the range
.... only using the column will lead the array formula into calculating
with empty cells ...and will result into #NUM ...

HTH
Cheers
Carim

 

[Carim]

Cynthia,

Just forgot to add that if you are facing changing ranges, best
solution is to define names and make sure to use dynamic ranges ...

HTH
Cheers

Carim

 

[Carim]

Cynthia,

For the sake of completeness, you could also use sumproduct()

1. =SUMPRODUCT(((O1:O10)-(Q1:Q10)=0)*1,(R1:R10))
2. =SUMPRODUCT(((A1:A10)>=TODAY()-90)*1,(R1:R10))

Cheers
Carim

 

[clynneo]

You have been so helpful Carim. I feel like I am SOOO close on this,
but I am not certain how to define the range. The spreadsheet will be
continually changing (it is basically to log data) and I want the
formula to take into account when new information is entered.

 

[Carim]

Hi,

Well basically defining dynamic range is done with following formula :

=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),1)

Best explanation is available from Debra's site :

http://www.contextures.com/xlNames01.html#Dynamic

HTH
Cheers
Carim