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!
 

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.

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,446
Office Version
  1. 365
  2. 2010
Platform
  1. 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,446
Office Version
  1. 365
  2. 2010
Platform
  1. 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
 

Watch MrExcel Video

Forum statistics

Threads
1,133,278
Messages
5,657,821
Members
418,414
Latest member
ECMdusty

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top