countif(s) with 'or' statement

nikegeo

Board Regular
Joined
Jul 23, 2010
Messages
52
Hi all - im looking to count cells based on multiple criteria and on of the criteria should have an 'or'
I have the following criteria:
I want a count of all cells with country values - Australia, New Zealand, or AUZ if the PitchWL range is "win"

thank!
 

Some videos you may like

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

loonylou

New Member
Joined
Apr 20, 2010
Messages
9
Hi,

You need a dcount formula: =dcounta(list,field name,criteria)

List: entire table of data
Field: heading for the field you will count, i.e. country name
Criteria: separate area in your spreadsheet that you will name "criteria". This area should have the same field headings as your table does for country and PitchWL & will have 3 lines:

Country PitchWL
Australia Win
New Zealand Win
AUZ Win

This is telling excel to count the rows that have Australia and win; or New Zealand and win; or AUZ and win.

Hope this helps!
 

nikegeo

Board Regular
Joined
Jul 23, 2010
Messages
52
thanks for the reply, however im trying to avoid having excess lines for the criteria.

I was hoping to accomplish this another way.

Again i have named ranges:

Markets: "Australia", "New Zealand", Or "AUZ"
PitchWL: "Pitch Won"

And count the market or client field range (they will never be empty) if it meets the above criteria
 
Last edited:

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
17,082
Office Version
365, 2010
Platform
Windows
thanks for the reply, however im trying to avoid having excess lines for the criteria.

I was hoping to accomplish this another way.

Again i have named ranges:

Markets: "Australia", "New Zealand", Or "AUZ"
PitchWL: "Pitch Won"

And count the market or client field range (they will never be empty)
If the named ranges are Markets and PitchWL then try this - confirm with ctrl+shift+enter, not just enter:
Code:
=SUM(IF((markets="australia")+(markets="New Zealand")+(markets="AUZ")*(PitchWL="pitch won"),1,0))
 

Brian from Maui

MrExcel MVP
Joined
Feb 16, 2002
Messages
8,458

ADVERTISEMENT

If you have 2003 or earlier

=SUMPRODUCT(--(ISNUMBER(MATCH(F1:F10,{"criteria1","criteria2","criteria3"},0))),--(G1:G10="Pitch won"))

If later than 2003, try COUNTIFS
 

nikegeo

Board Regular
Joined
Jul 23, 2010
Messages
52
If the named ranges are Markets and PitchWL then try this - confirm with ctrl+shift+enter, not just enter:
Code:
=SUM(IF((markets="australia")+(markets="New Zealand")+(markets="AUZ")*(PitchWL="pitch won"),1,0))
This counted all values for Australia + New Zealand + AUZ regardless of PitchWL="Pitch Won"
 

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
17,082
Office Version
365, 2010
Platform
Windows
This counted all values for Australia + New Zealand + AUZ regardless of PitchWL="Pitch Won"
Apologies, forgot a pair of parentheses. Try this (remember to confirm with ctrl+shift+enter):
Code:
=SUM(IF(((markets="australia")+(markets="New Zealand")+(markets="AUZ"))*(PitchWL="pitch won"),1,0))
 

nikegeo

Board Regular
Joined
Jul 23, 2010
Messages
52
JoeMo - thanks so much! the extra paranthesis worked. I was looking all over the place for a solution!

Im going to have to get my head around arrays.

thanks again
 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,106,509
Messages
5,511,712
Members
408,862
Latest member
sidneybc

This Week's Hot Topics

  • Turn fraction around
    Hello I need to turn a fraction around, for example I have 1/3 but I need to present as 3/1
  • TIme Clock record reformatting to ???
    Hello All, I'd like some help formatting this (Tbl-A)(Loaded via Power Query) [ATTACH type="full" width="511px" alt="PQdata.png"]22252[/ATTACH]...
  • TextBox Match
    hi, I am having a few issues with my code below, what I need it to do is when they enter a value in textbox8 (QTY) either 1,2 or 3 the 3 textboxes...
  • Using Large function based on Multiple Criteria
    Hello, I can't seem to get a Large formula to work based on two criteria's. I can easily get a oldest value based one value, but I'm struggling...
  • Can you check my code please
    Hi, Im going round in circles with a Compil Error End With Without With Here is the code [CODE=rich] Private Sub...
  • Combining 2 pivot tables into 1 chart
    Hello everyone, My question sounds simple but I do not know the answer. I have 2 pivot tables and 2 charts that go with this. However I want to...
Top