Sumif, Averageif, Countif, From a range

**Odysseus** · 09-09-2008, 08:06 AM

I'm trying to get the below to work, I'm tired and can't really tell if it should work or i've missed something simple :P

=COUNTIF(codejan07,MID(codejan07,12,1)="P")

So what I have is 10000 rows of data and one column (codejan07) has a 20 digit code. I need to count how many of those 10000 lines has the letter P in position 12 of the code. I know I can do this by adding another column but I would like to have it all in one cell..

Any ideas, can it be done or do I have to resort to plan "B"

Cheers

**NBVC** · 09-09-2008, 08:13 AM

Try:

=COUNTIF(codejan07,REPT("?",11)&"P"&"*")

or

=SUMPRODUCT(--(MID(codejan07,12,1)="P"))

**Odysseus** · 09-09-2008, 08:39 AM

=SUMPRODUCT(--(MID(codejan07,12,1)="P"))
Works a treat, thanks very much..

Ok, next step is not going to be as easy as I thought now.

In column G I have values. What I need to do is average the cost of G where "codejan07" = "P"..

**Odysseus** · 09-09-2008, 12:41 PM

Anyone able to help with my 2nd part there? I've tried and failed

Cheers

**NBVC** · 09-09-2008, 12:43 PM

=SUMPRODUCT(--(MID(codejan07,12,1)="P"),columnGRange)/SUMPRODUCT(--(MID(codejan07,12,1)="P"))

or =Average(If(MID(codejan07,12,1)="P",columnGRange) which needs to be confirmed with CTRL+SHIFT+ENTER not just ENTER.

Note: ColumnGRange must be same size as codejan07 range.