count in two columns (2K3)

[Jezza]

Sorry, I have searched but cannot think of the right terminology.

What is the formula if I want to count for the word Rushtent in column E and that there is a number 1 in column G, I have tried sum and sumproduct.

[HansV]

"Normal" formula:

=SUMPRODUCT((E1:E1000="Rushtent")*(G1:G1000=1))

Array formula (confirm with Ctrl+Shift+Enter):

=SUM((E1:E1000="Rushtent")*(G1:G1000=1))

[Jezza]

Thanks Hans

You know what the irritating thing is, I did this formula and it didn't work for me, I have just found that after using your formula and it still didn't work[gggrrrr]

Double checking I found the reason was due to my SQL extract having padding, so a trim did the job. Thanks for your help again.

[Jezza]

I have been playing with this formula and the one below works fine but I was wondering is there a way to shorten it.

I am looking for everything in range C1:3955 that does not equal 104 or 102491 or 102492


=SUMPRODUCT((Format!F$1:F$3955=E14)*(Format!C$1:Fo rmat!C$3955<>102491)*(Format!C$1:Format!C$3955<>10 2492)*(Format!C$1:Format!C$3955<>104))

[rory]

You can certainly do it if you put those values into a range of cells (say J1:J3):
<code>=SUMPRODUCT((Format!F$1:F$3955=E14)*(COUNTIF ($J$1:$J$3,Format!$C$1:$C$3955)=0))</code>
should work.
HTH

[HansV]

I don't know if it's really simpler, but you could put the values you want to exclude in a range of cells, say P1:P3, and use this formula:

=SUMPRODUCT((Format!F$1:F$3955=E14)*ISERROR(MATCH( Format!C$1:C$3955,P1:P3,0)))

If you name the range of exclusions Excluded (it can be a dynamic range, for more flexibility), the formula becomes

=SUMPRODUCT((Format!F$1:F$3955=E14)*ISERROR(MATCH( Format!C$1:C$3955,Excluded,0)))

[Jezza]

Hi Rory

I think this one will get my vote ( for this particular application) as I will be handing it over to an Excel savvy user who is using this to count Performance Indicators and they can adapt the range to fit their needs.

Hans, yours is great, thank you.