# Thread: count in two columns (2K3)

1. ## count in two columns (2K3)

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.

2. ## Re: count in two columns (2K3)

"Normal" formula:

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

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

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

3. ## Re: count in two columns (2K3)

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.

4. ## Re: count in two columns (2K3)

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))

5. ## Re: count in two columns (2K3)

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

6. ## Re: count in two columns (2K3)

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)))

7. ## Re: count in two columns (2K3)

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.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•