Excel Magic Trick 1197: OR Criteria: Don’t Double Count: DCOUNTA, COUNTIFS, or SUMPRODUCT?

preview_player
Показать описание

See how take two columns of county numbers and kip codes and show unique list of county codes vertically, and then for each county code, list zips codes horizontally.
1. (00:09) OR Criteria Double Count Problem when criteria are coming from Two Columns
2. (01:33) DCOUNT function formula
3. (03:35) COUNTIFS function formula (3 COUNTIFS in one formula)
4. (06:35) SUMPRODUCT function formula (Array Formula)
5. (10:28) Timing all 2 formulas
  1. excelisfun
  2. Excel
  3. Microsoft Excel
  4. Highline College
  5. Mike Girvin
  6. excelisfun
Рекомендации по теме
Excel Magic Trick 0:12:28

Excel Magic Trick 1197: OR Criteria: Don’t Double Count: DCOUNTA, COUNTIFS, or SUMPRODUCT?

Excel Magic Trick 0:06:59

Excel Magic Trick 1200: Conditional Format Grades To Assign Icons To 1 of 3 Groups (3 Methods)

Excel Magic Trick 0:02:25

Excel Magic Trick 1188: SUMIFS with Wildcards to Do Approximate Criteria or “Fuzzy” Criteria

Excel Magic Trick 0:06:57

Excel Magic Trick 1181: Formula to Count Cells with 4 or More Characters: COUNTIFS & SUMPRODUCT

Excel Magic Trick 0:05:58

Excel Magic Trick 1193: Count Distinct Words As They Are Entered In Column: Formula or Excel Table?

Excel Magic Trick 0:10:07

Excel Magic Trick 1201: Conditional Format Product With No Sales (5 Examples)

Excel Magic Trick 0:02:40

Excel Magic Trick 1194: VLOOKUP: Data Mismatch Makes Yields #N/A, What To Do?

Excel Magic Trick 0:06:11

Excel Magic Trick 1156: Count with Two Conditions / Criteria From Non-Proper Data Set (2 Methods)

Excel Magic Trick 0:08:22

Excel Magic Trick 1177: Flash Fill or CODE Formula Split First Last Name Based on 2nd Capital Letter

Excel Magic Trick 0:08:32

Excel Magic Trick 1198: Median for Each Quarter Using AGGREGATE Function

Excel Magic Trick 0:09:53

Excel Magic Trick 1175: Decaying Average in Standards-Based Grading in Excel

Excel Magic Trick 0:05:46

Excel Magic Trick 1246: Counting with Case Sensitive Criteria: EXACT & SUMPRODUCT

Excel Magic Trick 0:22:14

Excel Magic Trick 1472: Count Between (or Above and Below) Upper & Lower Limits (7 Examples)

Excel Magic Trick 0:06:37

Excel Magic Trick 1199: Formula to Extract Everything After Last Space: Two Formula Solutions

FILTERXML Function to 0:05:08

FILTERXML Function to Split Text into Cells. Excel Magic Trick 1685.

When a dog 0:00:14

When a dog takes a virtual roller coaster~

Excel Magic Trick 0:02:21

Excel Magic Trick 1240: Double Click To Copy Formula Down When Formula Is Not Next To Data Set

Custom Formatting in 0:10:23

Custom Formatting in Excel | Easy way to replace Conditional Formatting | Show variance in Excel

Centralizare tabele Excel 0:08:40

Centralizare tabele Excel folosind PowerQuery

Use Pivot Tables 0:04:57

Use Pivot Tables to Evaluate Sales

Learn MS Excel 0:21:29

Learn MS Excel -Video 428- Amazing Exercise on OFFSET ROW IF SMALL

Using SUMPRODUCT as 0:00:58

Using SUMPRODUCT as count function | SUMPRODUCT formula in excel | excel tutorial | #shorts

Excel - Provide 0:05:25

Excel - Provide Feedback to the Excel Team - Pivot Table Tabular as Default - Episode 1959

02 Using 0:04:44

02 Using Excel Menu Systems Microsoft Excel 2016 Beginner Course

Комментарии
Автор

Excel Magic Trick 1197: OR Criteria: Don’t Double Count: DCOUNTA, COUNTIFS, or SUMPRODUCT?
See how take two columns of county numbers and kip codes and show unique list of county codes vertically, and then for each county code, list zips codes horizontally.
1. (00:09) OR Criteria Double Count Problem when criteria are coming from Two Columns
2. (01:33) DCOUNT function formula
3. (03:35) COUNTIFS function formula (3 COUNTIFS in one formula)
4. (06:35) SUMPRODUCT function formula (Array Formula)
5. (10:28) Timing all 2 formulas

excelisfun
Автор

Hi Mike!

Another Option (Non CSE too):
=SUM(COUNTIF(A4:B13, {"Sioux", "Bobby Soxs"}))-COUNTIFS(A4:A13, D4, B4:B13, E4)

Or this (Non CSE):
=SUM(SIGN(MMULT(--(A4:B13=D4:E4), {1;1})))

Blessings!

JohnVergaraD
Автор

Watched this for the first time off the back of seeing Mynda Treacy's new one on Sumproduct using the
SIGN to avoid doubling, Hope you don't mind the mention.

williamarthur
Автор

That thing with the SUMPRODUCT is very important.
Thanks for another great video

לירןזיידמן-גי
Автор

Amazing use of the SUMPRODUCT function Mike! Thanks for sharing!

MySpreadsheetLab
Автор

Hey Mike
How can I delete double cout 

=VLOOKUP(1;{"1, 100";"2, 200"};2;0)

Reduce_Scan
Автор

Hi Mike
How do I find the first non-zero value inside match 
In other words
What is the value that was looking for out of order for the first number other than zero

=MATCH(;{0;0;0;1;0;2;3})

Reduce_Scan
Автор

Great explanation, as always. :-)
"+" or "*" as boolean logic is the best :-)
Thanks !!

BillSzysz
Автор

What about 2 different criteria from the same column?

chrisbenton
Автор

How were you able to time how long the formulas took?

nicholasniedzielski
Автор

'Love your instructional videos.  Watching this one, however, requires a comment on language.  "Criteria" is a plural word.  "Criterion" is the singular form.  Your column headings therefore should read "Criterion 1" and "Criterion 2".  From a friendly grammarian.

heatherlloyd
Автор

ExcellsFun is my uncle no kidding and his son Isaac Girvin is my cousin!

levelzeroD
Автор

Hi Mike
I want to count this number without duplication
In this example I want result 3
how this


Reduce_Scan