count only if

[Aaron]

=SUM(IF(Sheet1!$K$5:$K$705=A12,IF(Sheet1!$M$5:$M$705=1,"PSP*")))

I'm trying to get a number value on Sheet 2 for how many times a cell starts
with PSP but only if two cells over it states a predefined user name on
Sheet2 hence the A12 in the formula. Can anyone point me in the right
direction? I've asked alot of people and it seems to stump them. even when
they think they can do it it ussually results in an error or incorrect value.

exsample:

A B C
Joe 123 PSP12321323
Joe 132 INC12123424
Jack 123 PSP12132342
Jack 132 PSP12123424
Jack 123 INC12324213


The result I'm looking for is how many times does PSP show up next to a
given user

 

[T. Valko]

Try this:

=SUMPRODUCT(--(A1:A5=A12),--(LEFT(C1:C5,3)="PSP"))

Biff

 

[Aaron]

I adjusted what you sugested with :

=SUMPRODUCT(--(Sheet1!K5:K705=A12),--(LEFT(Sheet1!M5:M999,3)="PSP"))

to fit my particular spreadsheet. came back with "#VALUE!"
I must be missing an important step in making it work in my spread sheet.

 

[T. Valko]

=SUMPRODUCT(--(Sheet1!K5:K705=A12),--(LEFT(Sheet1!M5:M999,3)="PSP"))

The ranges *must* be the same size.

Biff

 

[Aaron]

I think that becuase I'm trying to display the results on Sheet2 when the
data is on sheet1 is the reason why some of these suggestions don't work. in
theory they work, but when i put it into practise adjusting where to look I
must be messing somehitng up along the way. Thanks in advance for any advise
you may have.

 

[Aaron]

Looks like that did the trick. Lol.. note to self. must be the same range.
for some stupid reason i didn't think it mattered. Guess thats why I'm on
here asking questions and your on the answering side.

Many thanks, you don't know how much time you've saved me

 

[T. Valko]

You're welcome. Thanks for the feedback!

Biff

 

[shail]

Do you mean

=SUMPRODUCT(--(Sheet1!K5:K705=A12),--(LEFT(Sheet1!M5:M999,3)="PSP"))

should be

=SUMPRODUCT(--(Sheet1!K5:K999=A12),--(LEFT(Sheet1!M5:M999,3)="PSP"))

otherwise the function won't work?

Do let me know. Thankyou!

Shail