# Thread: Countif, Multiple Colums with another Countif per column!

1. Registered User
Join Date
Jun 2013
Posts
3

## Unanswered: Countif, Multiple Colums with another Countif per column!

Hi there,

I have been using the following formula successfully for counting instances in rows that match all 3 criteria (spread across 3 columns):

=SUMPRODUCT(--(H120:H126="R02"),--(I120:I126="NO"),--(J120:J126="Lol"))

So this formula tells you how many rows contain "R02" AND "NO" AND "Lol" in the required columns and ignores anything which does not contain ALL 3

What if my "Lol" criteria was changed to a COUNTIF argument? Instead of looking at "Lol" as my criteria, I only want to look at cells which contain ANY value and ignore blank cells. this would be =COUNTIF(AH2:AH129,">0")

So Im looking to substitue my third criteria which was set to "Lol" for a countif greater than 0 argument.

This would give me a count on all rows which contain "R02", "NO" AND "anything at all in this third column, ignoring the blanks"

I am struggling with this one! :P Is there an excel wizard out there who could solve this one?

G

2. www.gvee.co.uk
Join Date
Jan 2007
Location
UK
Posts
11,445
Why not just use this:
Code:
`=SUMPRODUCT(--(A2:A9="R02"),--(B2:B9="NO"),--(C2:C9<>""))`
?

3. Registered User
Join Date
Jun 2013
Posts
3
You absolute legend. That works perfectly!

FYI - I started using excel about 3 months ago so I am just learning.

Whilst I have an excel wizard to hand, could I ask you 1 more question?

How can I make a cell change its value based on another Cell?

For example, I want Cell A1 to automatically say "Yes" if cell B1 Contains ANY value.

Graham

4. www.gvee.co.uk
Join Date
Jan 2007
Location
UK
Posts
11,445
Code:
`=if(b1<>"", "yes", "no")`

5. Registered User
Join Date
Jun 2013
Posts
3
Originally Posted by gvee
Code:
`=if(b1<>"", "yes", "no")`

Hi George,

is it ok to say I love you?

6. www.gvee.co.uk
Join Date
Jan 2007
Location
UK
Posts
11,445
I'd expect nothing less

7. Registered User
Join Date
Sep 2008
Location
London, UK
Posts
511
If you're using Excel 2007 or later then you can use COUNTIFS() instead of SUMPRODUCT().

Code:
`=COUNTIFS(A2:A9,"R02",B2:B9,"NO",C2:C9,"<>")`
The general advantages of using COUNTIFS() instead of SUMPRODUCT() are that it is a simpler formula and it is quicker to calculate (in this case the calculation time difference on such a small dataset and for a single formula would be negligable).

8. Registered User
Join Date
Aug 2013
Posts
2
Hi,

I know I must start a new thread, but somehow my excel problem is almost like this except that may column range is a date, meaning i need to validate first the date selected before running the count if.. Please help, I'm also new in creating formulas in excel. Thanks