Count the number of cells with 3 or less characters but match against criteria

[rayted]

Hi guys,

I have sheet 1 and I am trying to count the NUMber of cells with 3 or less characters, but this has to be matched by the country on both sheets

so I have a country in Cell A2, sheet2, and this country exists within coulmn G on sheet 1. I want to have a LEN or something formula please to count the number of cells in column H that have 3 or less characters. Also, I have stripped down the data in my spreadsheet, so this should included column I on sheet1 which has this: GeneralCommentsMy original spreadsheet has multiple filters, so I need the number of cells in column H from sheet 1 which have 3 or less words whilst matching the country and being for the filter GeneralComments. Thank you guys!

sheet attached

[oeldere]

Post a small Excel sheet (not a picture) showing realistic & representative sample data WITHOUT confidential information (10-20 rows, not thousands...) and some manually calculated results.

[rayted]

Hi oeldere, added more content, sorry i have updated the Original post, I need the count of 3 or less characters, not 3 or less words, which are relevant to relevant country and filter GeneralComments

I am trying a counit at present with no success

[oeldere]

Where do I find the manualy added expected results?

[ChemistB]

In A2 copied right

Please Login or Register  to view this content.

[rayted]

sorry attached

[Fluff13]

How about
=SUMPRODUCT((Sheet1_!$G$2:$G$13=A1)*(LEN(Sheet1_!$H$2:$H$13)<=3))

[Bo_Ry]

Please try

=COUNTIFS(Sheet1_!$G:$G,A1,Sheet1_!$H:$H,"<>????*")

[rayted]

Hi guys, thank you for your solutions but this does not factor in column I and the filter/other criteria being General Comments

[Fluff13]

Missed the col I part, how about
=SUMPRODUCT((Sheet1_!$G$2:$G$13=A1)*(LEN(Sheet1_!$H$2:$H$13)<=3)*(Sheet1_!$I$2:$I$13="GeneralComments"))

[rayted]

AMAZING TY all and fluff13!

[Fluff13]

You're welcome & thanks for the feedback.

[rayted]

Hi Fluff13.. sorry....

Can you check this out.. I have applied the formula successfully to my real data. I am just trying to verify the figures from the formula.

And there are 17 rows (which is the figure I want)
but the formula is showing 32 (because it is summing the number of characters)............

1
3
3
1
3
3
1
2
1
1
3
1
3
1
2
2
1

I would like to know how many rows (17 in this case) have 3 or less characters in cell H. Can you update/help with formula please? Does a countproduct instead of sum product exist?

[Fluff13]

The formula does sum the number of characters, it sums the number of rows that match the criteria

[rayted]

ok thanks, got it!