sum count if formula for three criteria that delivers a different number for each one

1. sum count if formula for three criteria that delivers a different number for each one

I have tried a million ways of looking for this and have done a work around by putting it in a hidden column but I'm sure there is another way! I have columns with either y, yy or yyy in each cell. I want the bottom row to add up the number of ys so if "y" then 1, if "yy", then 2, if "yyy" then 3 and add them up. like this:

y
yy
yyy
6

I have this so far

=SUM(COUNTIF(C2:C43,"y"),COUNTIF(C2:C43,"yy"),COUNTIF(C2:C43,"yyy"))

All that gives me is the sum of every occurence rather than a total of ys like this (I don't want this version):

y
yy
yyy
3

I'm sorry if none of that made the blindest bit of sense. Any help would be very gratefully received.

Thanks

2. Re: sum count if formula for three criteria that delivers a different number for each one

Assuming the y, yy, yyy are in A1:A3 you could use

Formula:
`Please Login or Register  to view this content.`

This is an array formula and you need to press Ctrl + Shift + Enter to compute

3. Re: sum count if formula for three criteria that delivers a different number for each one

This version will count all lower case "y"s in the range

=SUMPRODUCT(LEN(C2:C43)-LEN(SUBSTITUTE(C2:C43,"y","")))

If you want to count both "Y" and "y" use this one

=SUMPRODUCT(LEN(C2:C43)-LEN(SUBSTITUTE(LOWER(C2:C43),"y","")))

....or if the only possibilities are "y", "yy" or "yyy" then you can use this version

=SUM(COUNTIF(C2:C43,{"y","yy","yyy"})*{1,2,3})

4. Re: sum count if formula for three criteria that delivers a different number for each one

Ow wow that's amazing. Thank you sooo much. Am ever so grateful.

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1