[ms office - 2003] COUNTIF Fuction

[vunsev]

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.

[Michael]

Counting up all data is straight forward:

 

=SUM(B1:B5)

 

How do you want to verify the names are correct? It needs to reference against something.

[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.

 

Counting up all data is straight forward:

 

=SUM(B1:B5)

 

How do you want to verify the names are correct? It needs to reference against something.

[Michael]

I've tried and I cannot seem to get anything working! Frustrating! Have you had a look at mrexcel.com?

[gskelton]

Hi there,

 

How about this:

 

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

[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

[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 ?

[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

and your formula becomes

=dcount(datatable, 2,e1:f2)

 

Not sure what you're doing but this might be getting to the point where it would be easier to do with Access or other database. Excel is good but querying data like this is better done with the proper tool :-)

[mac_shinobi]

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.

 

how will you know that the name in column A is correct ?