CODEWITHSUNDEEP
excelexcel-formulacountif
david_10001
david_10001

Reputation: 492

How to use COUNTIF and COUNTA together in Excel?

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?

Upvotes: 4

Views: 30465

Answers (2)

Meon Faiz
Meon Faiz

Reputation: 31

A universal COUNTIF formula for counting all non-blank cells in a specified range:

=COUNTIF(range,"<>"&"")

This formula works correctly with all value types - text, dates and numbers.

So you might want to use

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

Upvotes: 3

user4039065
user4039065

Reputation:

Try,

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

Upvotes: 2

Related Questions