Converting IF statements for use in SUMIF/COUNTIF [or conditional formatting] Excel

Question

I've been trying to get to grips with SUMIF & COUNTIF functions in Excel recently, with limited success. I've realised the crux of the problem is that help pages give far too specific examples, including the official Office support.

I already know how to put together complex tests with multiple criteria already, using IF statements. What I really need is a guide to how to convert IF statements for use in such functions.

The real issue for me is what happens to cell references? I have a column of cells, each with some value for a given property. With an IF function I can go into the adjacent column, test the neighbouring cell using some criterion or set of criteria to find its value for a given property, and return an appropriate answer. I click and drag my formula down to check all the cells.

Eg. A1:A10 are the cells I'm testing. The property I'm checking is their length, whose value will be the number of characters. The appropriate answer will be whether the number of characters is above or below a threshold.

Put together; IF(LEN(A1)>50,"above","below")

Pasted into B1 and dragged down to B10 I get an array of answers.

Suppose I want to count all the cells which meet the condition, that's where I'm stumped. COUNTIF looks like I could just specify the range (A1:A10) and condition LEN(A1)>50 and get my answers. But what do I put inside LEN()? I want to go through and check for each cell in the range, how can I specify just one? Specifying none: LEN() or the range LEN(A1:A10) won't work.

For highlighting cells (conditional formatting), it's easy, just put the top left cell of the array, so LEN(A1), but that doesn't work either!

I hope that's made the problem clear. Obviously I could just have IF(LEN(A1)>50,1,0) in B1:B10, and SUM(B1:B10) in C1 or something, thus counting all the cells which match the criteria in the if statement. But that seems like a totally retrograde step which negates the benefits of COUNTIF entirely. Namely that it saves space and reduces complexity in the sheet by doing away with intermediate steps.

And I have at least 1 sheet for which that definitely won't work owing to the volatile nature of my array sizes; I wouldn't be able to fit the additional intermediate columns if I wanted to!

SO, can any IF-statement-style check be converted to work with COUNTIF/SUMIF, if so then how, and are there any other tips you could include in case someone with a similar problem comes searching? Thanks so much for answers and help!

Answer 1

Use SUMPRODUCT:

=SUMPRODUCT(1*(LEN(A1:A10)>50))

COUNTIF/SUMIF do not like it when you try to modify the range to be tested. You can do a lot with the criteria, but not the range. It is what is given up to use it in a non array form.