Excel COUNTIFS function with OR Criteria

#COUNTIFS
#CountMultipleCriteria
#COUNTIFSfunction

For an intro to Get & Transform (Power Query) try my Lynda/LinkedIn course:



My book: Guerrilla Data Analysis 2nd Edition

[wayneedmondson]

Hi Oz.. great stuff.. too bad you can't somehow get variables between the curly brackets in an array constant inside a function argument. My first solution was the more obvious: COUNTIFS(for A criteria)+ COUNTIFS(for D criteria) which of course works and gives the same OR condition result. Also, you can point to the face of the worksheet for the criteria if you want to be able to change it in the future based on cell entry. Never the less, your formula is way cool to coax the solution using curly brackets and a specific defined array constant. As always, thanks for the tricks and the inspiration to learn and create! Thumbs up!!

[HinekoAkahi]

Excel is magic and you cannot convince me otherwise.

[shadrackawunyo]

Hahahaahah. I just can't stop laughing. Very impressive delivery.

[nuthannuthan]

Can you please teach us how to do this operation in power query

[kayronze]

Awesome! Just had a few doubts,
1. Should the data compulsorily be in a table?
2. Should it be sorted in a descending fashion?
Can't wait to try it out. Kudos to your technique

[hariomgupta]

Sir please make full course of power query

[fromspenny]

Used this for my Soccer Spreadsheet counted all wins draws and losses for 20 teams in one formula awesome

[stevereed]

Thanks Oz. Rather than having the A or D, is it possible to have it another way round and have either Column B or C equal to X?

[nuthannuthan]

Hi can you please teach us how to perform the same operation in Power query.

[hozanr]

Fantastic I learned a lot of excel techniques because I need to use it in my Job but still I have difficulties to be good enough to come with something nice can you advise me how I can be professional, ???

[hobbycat]

As usual a great source of inspiration. Is there something to interpret from the 2 different answers (spill in one row vs spill in one column) ??

[puneetgogia]

Amazing. I did this with SUMIFS first.

[abdelkrimlemhajeb]

Hi, For which version of Excel 2016 or 365?

Thank's

[renu]

How to change cell font color
ex if ROW Ä VALUE < 0 THEN FONT COLOR "RED""
PLZ GUIDE ME .
THANK U

[wazeerr]

Unfortunately, it's not working with me, just got one result for A.