How to count a range of non-blank values using criteria

[sp5carter]

How can I count a range of non-blank rows based on criteria from a range of values in another column?

Column A has multiple occurances of values ranging from A through J in each row (ie: a,a,a,a,b,b,b,b,b,c,c,c,c,c,c,c,c,c,c,c,d,d,d,d,d,etc). orrucance rates of each value (a,b,c,etc) will vary and range length (# of rows) will vary.

Corresponding rows in column b are either blank or numeric values.

How can I either:
1)count the non-blank rows in coumn b based on a defined text value in column A (ex: all c's)
2)calculate an average of the values in column b based on all the occurances of a particular value in column A (ex: all c's)?

 

[Aladin Akyurek]

It seems you are confusing rows and columns. I guess you have values like a, b, c, etc. in column A and corresponding numbers in column B. If so...

1]

=SUMPRODUCT(--($A$2:$A$100="a"),--($B$2:$B$100<>""))

2]

=AVERAGE(IF($A$2:$A$100="a",$B$2:$B$100))

which you need to confirm with control+shift+enter, not just with enter.

 

[sp5carter]

Thank's for the tip on the average calculation - a very big help. The confirm (cntl, shift was the key - I need to research why / when to "confirm".

What if I wanted to do the same thing but using criteria from two columns? ie: If column A="a" AND column C="monday", then average column B? I've played around with the IF AND function, with no luck.

Regards
Steve


ray:

 

[Aladin Akyurek]

Thank's for the tip on the average calculation - a very big help. The confirm (cntl, shift was the key - I need to research why / when to "confirm".

What if I wanted to do the same thing but using criteria from two columns? ie: If column A="a" AND column C="monday", then average column B? I've played around with the IF AND function, with no luck.

Regards
Steve


ray:

Just add another conditional term...

=AVERAGE(IF($A$2:$A$100="a",IF($C$2:$C$100="monday",$B$2:$B$100)))