count based on multiple criteria 1

[flashbbeb]

Hi all,

I'm familiar with SUMPRODUCT, and have used it to count the number of rows meeting multiple criteria. In previous circumstances, I've had concrete cell values to point to, either a specific string or a cell reference.

In my current case, I'm doing something that's not as straight forward.

I am looking for a formula that will calculate:

1) the number of times in a column (F) part of a string occurs - no exact matches, so I'm need to parse each cell for specific text "ML".

2) In another column (K), but corresponding to the same record, if the value is "Completed".

So far I can only get the first part:

=SUMPRODUCT(--(ISNUMBER(SEARCH("ML",F1:F50000,1))))

My attempt to get a count based on BOTH criteria fails.

=SUMPRODUCT(--(ISNUMBER(SEARCH("WBT BAI",F1:F50000,1))),--(K2:K50000="Completed")

Can anyone please assist?

Thanks,
EB

 

[GlennUK]

One big error that you have is that the ranges don't match in length ... you have rows 1:50000 in one expression, and 2:50000 in the other.

Cheers, Glenn.

Beauty is in the eye of the beerholder.