How to use COUNTIF and COUNTA together in Excel?

excel excel-formula countif
david_10001 picture david_10001 · Mar 8, 2018 · Viewed 12.4k times · Source

I have a single column and I want to count how many rows do NOT contain "www." but at the same time are not blank. I have tried:

=COUNTIFS(E2:E79,"<>*www.*",E2:E79,"?*")

This seems to work, but only for text. It will not count columns if it has numbers. I have also tried:

=AND(COUNTIF(E2:E79,"<>*www.*"),COUNTA(E2:E79))

This just returns TRUE. Can someone please show me the correct formula I need to use?

Answer

user4039065 picture user4039065 · Mar 8, 2018

Try,

=COUNTIFS(E2:E79,"<>*www.*",E2:E79,"<>")

Related questions

How to use VLOOKUP result as COUNTIF criteria

I want to be able to determine how many times a person's particular ID (e.g. #123) has appeared in a separate column (e.g. column C) given a name (e.g. Bob). I have: Let column A consist of several …

Read More
How to count cells that only contain the VALUE 0 and not the result of a formula?

For the following sheet: A 1 0 2 0 as formula result 3 0 4 0 as formula result 5 0 as formula result 6 blank 7 0 How do I count only the cells with 0 entered as VALUES and not the cells with 0 as their formula result, i.e. COUNTIF(A1:A7,0 …

Read More
Count occurrences of values

I have a column of text values with repeated values. I want to create a new column of unique values (no repeats) and a column with the frequency of each of those values. What's the easiest way to do that? …

Read More