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

sp5carter

New Member
Joined
Sep 20, 2006
Messages
2
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)? :banghead:
 

Some videos you may like

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
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

New Member
Joined
Sep 20, 2006
Messages
2
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


:pray:
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
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


:pray:

Just add another conditional term...

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

Watch MrExcel Video

Forum statistics

Threads
1,111,910
Messages
5,541,538
Members
410,547
Latest member
htran4
Top