[SOLVED] Sumproduct vs Countif

jlm2 · #1 07-02-2009, 09:37 AM

I have a worksheet where I am trying to count the number of occurences of several text strings.

For example:
I'm trying to count how many times "paid in full" and "fully paid" occur in column A.

I have two formulas, and both seem to work, but since I don't really understand either of them, I'm wondering which I should use and how I would adapt it to include additional text strings. (Like adding "paid" to the list)

Here are my formuals (I didn't write either of them, another co-worker did)

=(COUNTIF(A:A,"paid in full"))+(COUNTIF(A:A,"fully paid"))

=SUMPRODUCT(--(A1:A50={"paid in full","fully paid"}))

Also, if there is another and easier way to do what I'm trying to do, I'd love to know.

Thanks for your help

NBVC · #2 07-02-2009, 09:39 AM

Try:

=SUMPRODUCT(--ISNUMBER(MATCH(A1:A50,{"paid in full","fully paid"},0)))

and just add "paid" to the list between { } brackets

jlm2 · #3 07-02-2009, 09:44 AM

thanks for the quick reply!!

but now I'm wondering how your formula is different than mine. Could you walk me through the differences, or maybe walk me through what your formula is doing.

I have numerous other spreadsheets where I will be attempting something similar and would like to understand your logic.

Also, I'm afraid some of the text strings have spaces occuring after the words, and the formula above doesn't capture them. How do i overcome this?

Thanks again!

NBVC · #4 07-02-2009, 09:58 AM

Actually it's not really different.... yours should work..
Mine checks to see that there are matches to the array and returns a position number if there is... then the position numbers get counted.

To ignore extra spaces.. use Trim

=SUMPRODUCT(--(TRIM(A1:A50)={"paid in full","fully paid"}))

jlm2 · #5 07-02-2009, 10:04 AM

Thanks again.

I don't mean to be a bother, but I really would like to fully understand the Sumproduct formula. I've read some stuff online but nothing really satifies my ignorance

For instance, I have no idea what the two dashes in the beginning of the formula (right before TRIM in your latest formula.

And finally, why is the Sumproduct formula better than the Countif? Or is it?

Hope I haven't asked too many stupid questions.

NBVC · #6 07-02-2009, 10:09 AM

Have a look at this excellent article on Sumproduct:

http://www.xldynamic.com/source/xld.SUMPRODUCT.html

Countif is used for one condition and as you can tell, you need to make multiple Countifs linked together to get what you need.. and the Trim() function can't easily be added in the Countif function

jlm2 · #7 07-02-2009, 10:44 AM

great link. Very helpful article.

Welcome to the Excel Forum

Forum Statistics