# COUNTIF Fuction

• 27th February 2009, 02:53 PM
vunsev
COUNTIF Fuction
I have a excel spreadsheet I have a list of names going down in column A and data going down in column B.

So basically I need to count the data in B but only if the name in column A is correct.
• 27th February 2009, 03:01 PM
Michael
Counting up all data is straight forward:

Code:

`=SUM(B1:B5)`
How do you want to verify the names are correct? It needs to reference against something.
• 27th February 2009, 03:16 PM
vunsev
I don't want to add up the contents of B column. I need to count it only if the names matches for example something like.

=COUNTIF(A:A, "Peter") AND (B:B, "3").

I only want it to count it if it matches both condtions.

Didn't explain it very well in my previous post.

Quote:

Originally Posted by Michael
Counting up all data is straight forward:

Code:

`=SUM(B1:B5)`
How do you want to verify the names are correct? It needs to reference against something.

• 27th February 2009, 03:57 PM
Michael
I've tried and I cannot seem to get anything working! Frustrating! Have you had a look at mrexcel.com?
• 27th February 2009, 04:27 PM
gskelton
Hi there,

COUNTIF provides for one criteria. If we count column A, where vehicle type is Cars:

=COUNTIF(A2:A10,”Cars”)

But what if we want to count of records where vehicle type is cars and the color is black? We cannot use the COUNTIF function for multiple criteria.

We need to use an array formula, which requires that we press Ctrl+Shift+Enter after typing it, instead of just Enter.

=SUM((A2:A10=”Cars”)*(B2:B10=”Black”))

Count of records with between 500 and 800 units sold.
This also requires an array formula, press Ctrl+Shift+Enter after typing it.

=SUM((C2:C10>=500)*(C2:C10<=800))

Because these formulas are Array Formulas (sometimes referred to as CSE formulas because we must Ctrl+Shift+Enter to enter them), they look a little different. In the formula bar, after it's been properly entered, the above formula looks like this.

{=SUM((C2:C10>=500)*(C2:C10<=800)) }

Can't remember where I got this tip, but kudos to them...

Gary
• 9th March 2009, 11:43 AM
tw15ns
In Office 2007 there is now a COUNTIFS function which, if I rememeber correctly, lets you supply more than one criteria. I haven't used it very much so can't give precise instruction but have a look and see if it helps.

Graeme
• 9th March 2009, 11:53 AM
mac_shinobi
cant you use the following 3

and if function to check if a vlookup is null or not null and then do the ifcount if the vlookup returns true or false or w/e and do it that way ?
• 9th March 2009, 02:08 PM
srochford
If I understand what you're trying to do, you need to use DCOUNT

Give your data range a name (makes the formula easier!), put the criterion you're using in a pair of cells with the field name on top, value underneath (eg cells E1 and E2):
Name
Peter
Enter a formula:
=dcount(datatable, 2,e1:e2)
- this says count the cells in field 2 of datatable which have a number in them and where the value in "name" is "peter"

If you want to count for name=peter and data=2 then you extend the criterion range:
Name Data
Peter 2