# Thread: Concatenating Functions (Excell 2003, Off Prof 2003, Win 2000)

1. ## Concatenating Functions (Excell 2003, Off Prof 2003, Win 2000)

Don't know if this is possible, but it'd be nice if it was...

On a grid of data, I can do COUNTIF(Range, >3) to get a count of, well, all cells in the range greater than 3. I have an additional column with either "Manager" or "Deputy/Director" and I want to modify this formula to do a test on that column to only count cells in the COUNTIF range if cells in the column equal Manager OR Director. is this possible?

2. ## Re: Concatenating Functions (Excell 2003, Off Prof 2003, Win 2000)

Do you mean that you want a COUNTIF with multiple criteria? This can be realized by using SUMPRODUCT. For example, the formula
<code>
=SUMPRODUCT((A2:A100>3)*(B2:B100="Manager"))
</code>
will count the number of rows in the range 2-100 where the value in column A is greater than 3 *and* the corresponding value in column B equals "Manager".

3. ## Re: Concatenating Functions (Excell 2003, Off Prof

hmm - well I tried that and it seems to not work. I don't want to add the data in the cells but just count all cells with data in specific ranges. I am analyzing results from a survey with weighted questions and (bad = 1, not so bad = 2, neutral = 3, ok = 4, great = 5) so the 'value' indicates the selected choice. there's a formula for deriving the average choice based on a sum of the scores but I need to extract counts based on a range of responses. I can already do all this, but I wanted to check my results so want to do the same thing differently.

4. ## Re: Concatenating Functions (Excell 2003, Off Prof

The formula I posted does count cells, even though it uses SUMPRODUCT. Could you try to explain more clearly what you want to accomplish?

5. ## Re: Concatenating Functions (Excell 2003, Off Prof

Whoops - sorry - late in the day... you are correct of course. I thought I had checked but Excel loves to re-write your ranges when you move cells around. I reset the ranges and the numbers all add up now.

Thanks!

6. ## Re: Concatenating Functions (Excell 2003, Off Prof 2003, Win 2000)

Hello Hans
Is it possible to incorporate an OR function into this formula such that the row would be counted if column B contained either "Manager" or "Director"? I have been playing with this problem sans success.

T.I.A.

7. ## Re: Concatenating Functions (Excell 2003, Off Prof 2003, Win 2000)

You can add the results of two COUNTIF formulas:

=COUNTIF(B2:B100,"Manager")+COUNTIF(B2:B100,"Direc tor")

And with multiple conditions, you can add the results of two SUMPRODUCT formulas:

=SUMPRODUCT((A2:A100>3)*(B2:B100="Manager"))+SUMPR ODUCT((A2:A100>3)*(B2:B100="Director"))

or you can add within the SUMPRODUCT formula:

=SUMPRODUCT((A2:A100>3)*((B2:B100="Manager")+(B2:B 100="Director")))

8. ## Re: Concatenating Functions (Excell 2003, Off Prof 2003, Win 2000)

Thank you Hans <img src=/S/thumbup.gif border=0 alt=thumbup width=15 height=15>

9. ## Re: Concatenating Functions (Excell 2003, Off Prof

This should also work:
=SUMPRODUCT((A2:A100>3)*(B2:B100={"Manager","Direc tor"}))
FWIW

10. ## Re: Concatenating Functions (Excell 2003, Off Prof

Thanks Rory

I was surprised to find the squiggly brackets usable with a standard "Enter" as opposed to the "Ctrl + Shift + Enter" I associate with array functions. I believe that I now understand.

11. ## Re: Concatenating Functions (Excell 2003, Off Prof

Don,
I should have added a caveat that you should only use that syntax once within a SUMPRODUCT formula or you will get incorrect results. If you need more than one OR condition, use the addition syntax that Hans used.
HTH

12. ## Re: Concatenating Functions (Excell 2003, Off Prof

Thank you for the additional info Rory.

13. ## Re: Concatenating Functions (Excell 2003, Off Prof

Nice pun! <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

14. ## Re: Concatenating Functions (Excell 2003, Off Prof

None intended.

#### Posting Permissions

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