countif(s) with 'or' statement

[nikegeo]

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!

 

[loonylou]

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]

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

 

[JoeMo]

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:

=SUM(IF((markets="australia")+(markets="New Zealand")+(markets="AUZ")*(PitchWL="pitch won"),1,0))

 

[Brian from Maui]

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]

If the named ranges are Markets and PitchWL then try this - confirm with ctrl+shift+enter, not just enter:

=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]

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):

=SUM(IF(((markets="australia")+(markets="New Zealand")+(markets="AUZ"))*(PitchWL="pitch won"),1,0))

 

[nikegeo]

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