# count if two columns equal critera across multiple columns and rows

1. ## count if two columns equal critera across multiple columns and rows

Howdy,

If I have the following:

Fred none Amy early
John late Dave late
Kim early Sam early

I want to know how many rows where both people are listed as either "none" OR "late"

So in this example my answer should be 2 as John & Dave both have late and Brad & Lisa have none & late.

Thanks in advance for any help!

2. Hi Taylor_1978,

See attached where I've created a formula in column E and then filtered on it.

HTH

Robert

3. if you assume your sample data was in range A1:D4

=SUMPRODUCT(--(ISNUMBER(MATCH(\$B\$1:\$B\$4,{"none","late"},0))),--(ISNUMBER(MATCH(\$D\$1:\$D\$4,{"none","late"},0))))

or even

=SUMPRODUCT(--(ISNUMBER(MATCH(\$B\$1:\$B\$4&":"&\$D\$1:\$D\$4,{"none:none","early:early","none:early","early:none"},0))))

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1