COUNTIF with DATE Logic

  • Thread starter Thread starter ExcelMonkey
  • Start date Start date
E

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
 
What role does the hyphen "--" play in this formula?

=SUMPRODUCT(--(YEAR(A1:A10000)=2004),--(MONTH(A1:A10000)=1),--(B1:B1000
0>1))
 
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
 
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.
 
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!
 
Back
Top