Excel Complex formula questions 2

[satinsilhouette]

hi,

I want to check the value of two cells and then do a count if they are true:

Cell one has a class number in it. Cell two needs to be in a date range, for instance, in January, February, etc. So 1/1/2006 to 1/31/2006 range.

Then if both of these are true, I need to count a different field.

SO far I have:

=IF(Data!A2:A400 = "CR 10 RD1 and Data!C2:C400 between #01/01/2006# and #01/31/2006#,COUNTIF(Data!A2:A400,Data!A2,0))

But I am getting an error. Can someone help?

 

[SkipVought]

Hi,

First, it's best to put your date criteria in cells; lets say A1 & A2
[tt]
=sumproduct((Data!$A$2:$A$400="CR 10 RD1")*(Data!$C$2:$C$400>=$A$1)*(Data!$C$2:$C$400<=$A$2))
[/tt]
BTW,
[tt]
Data!$A$2:$A$400="CR 10 RD1"
Data!A2:A400=Data!A2
[/tt]
can only be truw is the former is true.


Skip,

_{[red]Be Advised![/red] The only distinction between a bird with one wing and a bird with two, is merely...
a difference of A Pinion!}

 

[bluedragon2]

=SUMPRODUCT((A2:A400="CR 10 RD1")*(C2:C400>38717)*(C2:C400<39083)*(D2400))

Where D2400 is the column that you want to count



[Blue]Blue[/Blue] [Dragon]

If I wasn't Blue, I would just be a Dragon...

 

[satinsilhouette]

Thank you both for your answers:

Can I set a date range in this like:

=SUMPRODUCT((Data!$A$2:$A$400="CR 10 RD1")*(Data!$C$2:$C$400>=#01/01/2006#)*(Data!$C$2:$C$400<=#01/31/2006#))

 

[SkipVought]

use DATEVALUE
[tt]
DATEVALUE("1/1/2006")
[/tt]

Skip,

_{[red]Be Advised![/red] The only distinction between a bird with one wing and a bird with two, is merely...
a difference of A Pinion!}

 

[satinsilhouette]

Skip,

then >= datevalue("1/1/2006")???

Sorry, I can code so well in other apps, but haven't done much in excel.

Thanks,
satinsilhouette

 

[SkipVought]

Did you try it? Works for me!

Skip,

_{[red]Be Advised![/red] The only distinction between a bird with one wing and a bird with two, is merely...
a difference of A Pinion!}

 

[SkipVought]

....which is WHY I suggested

First, it's best to put your date criteria in cells; lets say A1 & A2

Skip,

_{[red]Be Advised![/red] The only distinction between a bird with one wing and a bird with two, is merely...
a difference of A Pinion!}

 

[satinsilhouette]

Here is what I have so far:

=SUMPRODUCT((Data!$A$2:$A$400="CR 10 RD1")*(Data!$C$2:$C$400>=DateVaule("1/1/2006"))*(Data!$C$2:$C$400<=DateVaule("1/31/2006")))

But I am getting a #Name3 error.

 

[satinsilhouette]

The date criteria I am checking against is in the column C cells. The number of the class is in the Column A cells. The count needs to come of the column G cells.

 

[SkipVought]

Date[red]VALUE[/red]

Skip,

_{[red]Be Advised![/red] The only distinction between a bird with one wing and a bird with two, is merely...
a difference of A Pinion!}

 

[satinsilhouette]

Fabulous! I can't type either! LOL!

 

[satinsilhouette]

I just wanted to let you know, I got the formula to work, then set up ever row and since it references all cells, as excel does, all the copying adjusted to the cell row I was on, this made it a snap! Thanks much SkipVought and bluedragon2

 

[satinsilhouette]

Here's another question on the same formula, I need to leave room for this to grow. I tried expanding the formula to grow to $c$800. See below:

=SUMPRODUCT((Data!$A$2:$A$400="CR 10 RD1")*(Data!$C$2:$C$800>=DATEVALUE("1/1/2006"))*(Data!$C$2:$C$400<=DATEVALUE("1/31/2006")))

But I get a N/A error. Is there a way to handle the null in this statement so the data can grow in the spreadsheet and I can account for it?

 

[SkipVought]

Check out

How can I rename a table as it changes size faq68-1331

Use the OFFSET Function to define a Dynamic Range Name.

So if your range names are DataValues and MyDate, , then
[tt]
=SUMPRODUCT((DataValues="CR 10 RD1")*(MyDate>=DateValue("1/1/2006"))*(MyDate<=DateValue("1/31/2006")))
[/tt]

Skip,

_{[red]Be Advised![/red] The only distinction between a bird with one wing and a bird with two, is merely...
a difference of A Pinion!}

 

[satinsilhouette]

Okay thanks for that tip, but seems I can't get the syntax correct:

=OFFSET(CourseID,(A3:A5000),$A$A,0,0)

The sheet this data is in is Dat
The CourseID is in column A
The startdate is in column c
the count I am doing is in column g

So, this will grow row wise.

 

[SkipVought]

Assuming that you have a HEADING in A1 AND every row containing data has a VALUE in column A...
[tt]
DataValues
=OFFSET(Data!$A$1,1,0,Counta(Data!$A:$A)-1,1)

MyDate
=OFFSET(Data!$A$1,1,2,Counta(Data!$A:$A)-1,1)
[/tt]


Skip,

_{[red]Be Advised![/red] The only distinction between a bird with one wing and a bird with two, is merely...
a difference of A Pinion!}

 

[satinsilhouette]

Not every row will have a value in column A, it will grow as they fill out their spreadsheet.

Am I using this in the data sheet and then referencing the functions in the summary sheet in the above formula for counting?

Patience please, I am learning something completely new to me. Thanks!

 

[SkipVought]

Then you need the COUNTA column to reference one that has a data value for each row of data; if not A, then WHAT?

Skip,

_{[red]Be Advised![/red] The only distinction between a bird with one wing and a bird with two, is merely...
a difference of A Pinion!}

 

[firefytr]

I recommend against the OFFSET/COUNTA method. One, OFFSET is a volatile function, and second the COUNTA will not give you accurate results if you have a non-contiguous range or blanks in your data data.

Instead, check out the methods Bob Phillips lines out on his site ..

http://www.xldynamic.com/source/xld.LastValue.html

-----------
Regards,
Zack Barresse