CODEWITHSUNDEEP
excelcountconditional-statements
SugaKookie
SugaKookie

Reputation: 790

Excel Count cells with number if certain conditions are met

I want to find the percent of cells in the column NO2 (for example) that contain a number, only considering cells that fall in a certain month during a certain hour - so for example, how many cells contain numbers in the NO2 column during hour one during each day in January.

I've been trying to do 1 - COUNTIF(condition,"*")/COUNTA(condition), but I'm having trouble writing up the condition part.

Condition (considering three months). It gives me a $VALUE! error if I try to put this into COUNTIF

IF($C$1:$C$8985="January",
IF($B$1:$B$8985="1:00",$E$1:$E$8985,""),
IF($C$1:$C$8985="February",
IF($B$1:$B$8985="1:00",'$E$1:$E$8985,""),
IF($C$1:$C$8985="December,
IF($B$1:$B$8985="1:00",$E$1:$E$8985,"")

enter image description here

Upvotes: 2

Views: 178

Answers (1)

K.Dᴀᴠɪs
K.Dᴀᴠɪs

Reputation: 10139

Try using =SUMPRODUCT():

=SUMPRODUCT(--(A:A="January"), --(B:B=TIMEVALUE("1:00")), --(ISNUMBER(C:C)))

Where you'd replace:

  • A:A with your month column
  • B:B with your time column
  • C:C with your NO2 column

Adding in 'OR Logic'

If you are wanting to use OR logic, you can simply "add" the criteria to look like
--((A:A="January")+(A:A="February")).

=SUMPRODUCT(--((A:A="January")+(A:A="February")), --(B:B=TIMEVALUE("1:00")), 
    --(ISNUMBER(C:C)))

So this says Column A can either be January OR February, and Column B has a time value of 1:00, and column C is a numeric value.

This essentially creates three arrays - 1 for each of your criteria. It would look something like this:

{"January", "January", "January", "February"}  
{"1:00 AM", "1:00 AM", "2:00 AM", "1:00 AM" }  
{      1.0,       1.3,    "text",         4 }

It converts these into True/False

{True, True, True, True}
{True, True,False, True}
{True, True,False, True}
 ^^^^  ^^^^        ^^^^     << Counts the number where ALL are 'True'

When each of the 'indexes' all return true, it is added into your function.

If these arrays do not have the same length, then your SUMPRODUCT() function will return an error. 

{True, True, True, True}
{True, True,False, True}
{True, True,False}
 ^^^^  ^^^^        *ERR    << Returns error as the last array was smaller

Upvotes: 1

Related Questions