COUNTIF with DATE Logic

[ExcelMonkey]

I have an array with hourly data for an entire year. Normally I use
CountIf on this array to ascertain how many occurences occur ove
certain data ranges over the entire year. So assuming the data is i
B1:B8760. I would Count the number of occurences for the whole yea
where data is greater than 1:

Entire Year =COUNTIF($B$1:$B$8760,>1)

However I now want to use this COUNTIF to assess how may occurence
occur over certain data ranges in certain months. The easy way to d
this is to build 12 separate COUNTIFs and change the array:

January =COUNTIF($B$1:$B$744,>1)
February =COUNTIF($B$745:$B$1416,>1)
etc......

However I have to do this over multiple years taking into account lea
years. I added a new column field called month and assigned the righ
month to each hourly data element. How do I write one COUNTIF statmen
so that it always pulls from the entire holry array and discern
between the months? I will express the ouput over 12 cells in a
column as follows:

Year 1
January-----COUNTIF(DATELOGICARRAY, >1)
February----COUNTIF(DATELOGICARRAY, >1)
March--------COUNTIF(DATELOGICARRAY, >1)
April----------COUNTIF(DATELOGICARRAY, >1)
May----------COUNTIF(DATELOGICARRAY, >1)
June---------COUNTIF(DATELOGICARRAY, >1)
July----------COUNTIF(DATELOGICARRAY, >1)
August------COUNTIF(DATELOGICARRAY, >1)
September-COUNTIF(DATELOGICARRAY, >1)
October-----COUNTIF(DATELOGICARRAY, >1)
November--COUNTIF(DATELOGICARRAY, >1)
December--COUNTIF(DATELOGICARRAY, >1)

Can anyone help me with this?

Thank-yo

 

[Frank Kabel]

Hi
if your date is in column A try (for January 2004)
=SUMPRODUCT(--(YEAR(A1:A10000)=2004),--(MONTH(A1:A10000)=1),--(B1:B1000
0>1))

you may also consider using a pivot table for this kind of reports. See
http://www.cpearson.com/excel/pivots.htm
http://peltiertech.com/Excel/Pivots/pivotstart.htm
http://www.contextures.com/xlPivot02.html
http://www.ozgrid.com/Excel/excel-pivot-tables.htm

This will eliminate the need for multiple formulas

 

[ExcelMonkey]

What role does the hyphen "--" play in this formula?

=SUMPRODUCT(--(YEAR(A1:A10000)=2004),--(MONTH(A1:A10000)=1),--(B1:B1000
0>1))

 

[Frank Kabel]

Hi
the unary operator coeraces the boolean value to real numbers (TRUE=1,
FALSE=0)

 

[ExcelMonkey]

How will a sumproduct help me if I want to count the occurences.
understand that the sumproduct will effectively multiple the True (1
False (0) entries by each data element. This would be a version of th
SUMIF. But this will not allow me to count the occurences. That is
need a variation on COUNTIF not SUMIF. Am I missing something here?

Thank

 

[JE McGimpsey]

The first thing you're missing is that you should actually try the
solution to see that it *does* work, rather than simply questioning
whether it will work.

After that, if you can't figure out *why* it works, ask the question,
and the answer you will receive is that the unary minuses coerce the
boolean TRUE/FALSE values to 1/0. When the arrays are multiplied, a 1 is
generated for every instance of all of the conditions being TRUE, and a
0 for any instance in which a condition is FALSE. Summing the 1's is
equivalent to counting the instances for which all the conditions are
TRUE.

 

[Peo Sjoblom]

Why don't you try the formula first?
It's not similar to SUMIF

Since the arrays will return 1 or 0 then multiplied against each other those
where
all 3 conditions are true will return 1*1*1 = 1, partly true 1*0*1 = 0 or
0*0*0 = 0 etc.
finally SUMPRODUCT will sum all 1s thus returning the occurrences where all
3 conditions are
true and disregard the rest!

 

[ExcelMonkey]

Monkey-See Monkey-Do

Thank