Countif problem!

[JohnAcc]

Hi All,

I have a countif formula that I'm struggling with. I thought it would be really easy and I've come very unstuck! My problem is thus:

I have a range from B3:B400 that contains the text "Yes","Starts","-" or "Ends". I have a range from N3:N400 that contains department names.

I want to be able to count the number rows that contain the text "Yes" or "Starts" in B3:B400 if the department name in column N equals "New Business" for example.

Any help would be greatly appreciated!

Rgds, John

 

[GlennUK]

I would do a couple of SUMPRODUCT functions added together for this. It will be something like this:

=SUMPRODUCT((B3:B400="Yes")*(N3:N400="New Business"))+SUMPRODUCT((B3:B400="Starts")*(N3:N400="New Business"))

Cheers, Glenn.

 

[Harlequin007]

An alternative John is to use Access as you will probably want to use other queries here also.

You could simply link the Ecel spreadsheets into a database and maniuplate the data there.

----------------------------
Cogito Ergo Sum

http://www.discworks.co.uk

----------------------------

 

[xlbo]

Alternatively, just whack the whole lot into a pivottable with Dept name as row field 1 and col B as row field 2

Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question

 

[xlbo]

D'OH - and COUNT of Dept Name as VALUE field

Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question

 

[01Patrik]

Why not simply use DCOUNTA function?


=DCOUNTA(B2:N400,14,'Sheet 2'!A1:B2)

Where row 2 is the row where I put my column labels,"14" is the number that Column 'N' is representing in the range, and "'Sheet 2'!A1:B2" is the reference to a range where I put the following criteria:


A B
1 ='sheet 1'B2 ='sheet 1'N2
2 Yes New Business


'sheet 1'B2 / N2 beeing the column label.

The database functions are very simple, and extremely useful!




// Patrik