Counting and other formulae with two criteria

[jesma12]

Hi all, first of all i would like to thank in advance for helping me.
I have a problem which i am trying to solve all day but doesnt seem to work.​

columnA Column B<TABLE style="WIDTH: 96pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=128 border=0><COLGROUP><COL style="WIDTH: 48pt" span=2 width=64><TBODY><TR style="HEIGHT: 15pt" height=20><TD class=xl1061 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; WIDTH: 48pt; BORDER-BOTTOM: #f0f0f0; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" width=64 height=20>2.112</TD><TD class=xl1061 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; WIDTH: 48pt; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent" width=64>Area 3</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl1061 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>1.26</TD><TD class=xl1061 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent">Tal </TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl1061 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>6.45</TD><TD class=xl1061 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent">Area 1</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl1061 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>2.661</TD><TD class=xl1061 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent">Area 1</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl1061 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20></TD><TD class=xl1061 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent">Tal</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl1061 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>5</TD><TD class=xl1061 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent">Tal</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl1061 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>5</TD><TD class=xl1061 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent">Tal</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl1061 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>1</TD><TD class=xl1061 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent">Tal</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl1061 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20></TD><TD class=xl1061 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent">Tal</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl1061 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20></TD><TD class=xl1061 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent">Tal</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl1061 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>5.701</TD><TD class=xl1061 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent">Area 3</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl1061 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20></TD><TD class=xl1061 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent">Area 3</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl1061 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>3.549</TD><TD class=xl1061 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent">Area 3</TD></TR></TBODY></TABLE>

I have two columns of data that i need to use to do basic statistics
The two columns are as follows
This is just a part of my huge file
As you can see there is data in some of the column A and no data in some
These are the problems i am trying to solve
1) count the number of values in column in A that have data for Tal in colB
I used =sumproduct((A1:A10>0)*(B1:B10="Tal") but i get 0 as the result
2) Perform an average,min.max,std deviation on the same values, ie column A values for Tal in column B​

Also, i know i can filter and sort and do it easy way but i need to do it using formulae cos this file needs to be kept uptodate to new information added
Thanks again​

 

[VoG]

Try

=sumproduct(--(A1:A10<>""),(B1:B10="Tal"))

 

[jesma12]

I still get zero. I dont know why

 

[Marcelo Branco]

Hi,

Maybe you have leading and/or trailing spaces in column B. Try this just in case...

=SUMPRODUCT(--(A1:A14>0),--(ISNUMBER(SEARCH("Tal",B1:B14))))

M.

 

[jesma12]

Worked perfectly!! Thank you so much!

 

[Marcelo Branco]

Worked perfectly!! Thank you so much!

You are welcome.

But this is only the first part...

M.

 

[jesma12]

Yes true. I am working on the rest and could really appreciate some help.
I did the mean by myself. I used the formula =Averageif(B2:B10,"Tal*",A2:A10) and it worked but for the other functions, like percentile, i dont know how to include the if function. I need to find the percentile, median, max, min and standard deviation for the values we just counted

 

[Marcelo Branco]

To get the average, min, max etc you need array-formulas like

=AVERAGE(IF($A$1:$A$14>0,IF(ISNUMBER(SEARCH("Tal",$B$1:$B$14)),$A$1:$A$14)))
confirmed with Ctrl+Shift+Enter

you can copy down this formula for 3 rows and change AVERAGE to MIN, MAX, STDEV...

HTH

M.

 

[jesma12]

Thank you! It worked! The only thing that did not work is the percentile. P10 and P90 is what i need to find. I tried different formats of the same formula with percentile but no success

 

[Marcelo Branco]

I have never used the PERCENTILE function, but has taken a look at the help and

=PERCENTILE(range,k)
k is a number between 0 and 1

So use the same array and include k accordingly

M.