Excel countif with 3 conditions

June 19, 2010 at 09:03:37
Specs: Windows XP
I have 3 conditions which I'm trying to use the countif formula.

Column d:

Column f: "*" (I need to pull all of the names, becasue some have numbers and some are blank. which is the reason I am using a wild card.

Column g:
Full name

What is the correct formula? This is what I have but it's not working.

=SUM((D16:D65000="Print PRISM Appl")*(F16:F65000="*")*(G16:G65000="Full name of applicant"))

See More: Excel countif with 3 conditions

Report •

June 22, 2010 at 17:39:27

I am not sure what you are trying to achieve.

Looking at the formula I am guessing that you are trying to count the number of rows where:
1. the cell in column D contains "Print PRISM Appl"
2. the cell in column G contains the text "Full name of applicant"

3 as to column F, I am not sure how this comes into the equation - if you are testing these cells for names, numbers and blanks:all of the names, because some have numbers and some are blank
then you will be counting all the cells - those with names, those with numbers and those that are blank.

If you want to count the number of rows which have "Print PRISM Appl" in D and "Full name of applicant" in G, then the SUMPRODUCT() function should do it.

I do have to ask whether you actually have almost 65,000 rows of data. If not limit your range to either the actual rows in use, or only a little larger to allow for new data.

=SUMPRODUCT((D16:D6000="Print PRISM Appl")*(G16:G6000="Full name of applicant"))
will count the number of rows with both those text strings on the same row

Can you give a clear description of what data is in the three columns and what you want to achieve, e.g. Count how many rows have data in column F, the text "Print PRISM Appl" in column D and "Full name of applicant" in column G.
The data in column F could be text or a number, but don't count the row if cell F is empty.


Report •
Related Solutions

Ask Question