Countifs with dates 2

[wreded]

i'm using Excel 2007 and have discovered the COUNTIFS() function that suits my needs nearly perfectly.
My problem is in getting the data i want from a sheet within the workbook.
Data sheet consists of 7 columns; Column 1 is a data, Column 3 is a number, Column 5 is a number, Column 6 and 7 are text.

i can calculate the dates in the Data sheet, but not in Sheet 1 or any other sheet i try it in.

=MONTH(TODAY()))

Gives me the current month in the current sheet. i can add and subtract to get other months.

=countif(Data!$A$2:$A$280,month(today()))

Should give me a count of all the current months in the data sheet and return the result in Sheet 1, but it doesn't.

i can get all the other data i need, display it as required, etc. but these date things are driving me batty.

Am i missing something here?

 

[AnotherHiggins]

=Month(Today())
returns the number 4. That's not a date. That means you're asking for how many times the number 4 appears int he range Data!$A$2:$A$280. I'd bet that range contains dates.

See faq68-5827 for info on how Excel stores dates and times. The gist is that dates are whole numbers (based on the number of days since 1/1/1900) and times are decimals (based on percentage of a 24 hour period). Today is stored as 39912 (39,912 days since 1/1/1900).

39912 <> 4.

Try this instead:
[tab]=SUMPRODUCT(--(MONTH(Data!$A$2:$A$280) = MONTH(TODAY())))

[tt][blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[wreded]

THAT'S IT!!
It's funny how computers do what You say instead of what You mean. They're so darned literal.
Thanks for clearing this up for me.
Now to make it work with Excel 07's COUNTIFS() function.
Thanks,
Dave

 

[AnotherHiggins]

Glad to help.

But why do you need to use CountIfs? The SumProduct function I provided will return the count of records in April.

[tt][blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[wreded]

To be truthful i developed this particular thing in Excel 03 and the SumProduct function worked well. i'm at work and using Excel 07 and found the CountIfs function and it's my new sweetheart (so to speak).
Where i'm going is to build this spreadsheet once and then forget about it. i have to take a bunch of information from an Access database (got that part figured), add a new sheet with different information, merge it all together in a sort of "cover sheet" that presents the information in an easily digestible format.
It's easier for me to explain the CountIfs function than SumProduct and a little easier to code for.
No success yet, but it's only 2:00.
Thanks again,
Dave

 

[SkipVought]

Seems very similar...
[tt]
=COUNTIFS(Singer,"Madonna",Minutes,"<=4",Minutes,">=3")
=SUMPRODUCT((Singer="Madonna")*(Minutes<=4)*(Minutes>=3))
[/tt]
COUNTIFS example lifted from

http://office.microsoft.com/en-us/excel/HA102369931033.aspx

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[wreded]

Ok, i can see the difference. Maybe not easier to code after all.
i can get it to work like this:

=SUMPRODUCT(--((MONTH(Data!$A$2:$A$280)=MONTH(TODAY()))*(Data!$G$2:$G$280="Arrival")))

But let me complicate it some more...

=SUMPRODUCT(--((MONTH(Data!$A$2:$A$280)=MONTH(TODAY()))*(Data!$G$2:$G$280=Mid("D",1,1)))

If something departs or diverts it doesn't get here so i want to further differentiate between "Departures" and "Diversions" by checking only as many characters in ColumnG as i need rather than specifying all the different permutations possible (depart, divert, delay, etc.)
This one doesn't work but specifying each permutation does.
Could i also ask a question? Why the leading "(--" before the "((Month(" function. It doesn't work without it but doesn't appear to make any difference in following specifics.
Thanks,
Dave

 

[SkipVought]

Try this
[tt]
=SUMPRODUCT(--(MONTH(Data!$A$2:$A$280)=MONTH(TODAY()))*(Data!$G$2:$G$280>="D")*(Data!$G$2:$G$280<"E"))
[/tt]
-- is a double unary operator that coerces the calculation.

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[mintjulep]

-- is a double unary operator that coerces the calculation.

What?

 

[AnotherHiggins]

It's a way to coerce that part of the formula to evaluate as a number so the SumProduct has something to sum.

It's similar to how you can multiply by 1 or add 0 to force numeric strings into actual numbers.

The double unary is taking the negative of the negative of what's inside the parentheses, which forces it to become a number without changing the value.

[tt][blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[wreded]

Skip,
Worked like a charm. Thanks so much for Your assistance and making things clear.
Dave