Excel Magic Trick 1104

Add with 6 Criteria (AND and OR Criteria) with Criteria/Data Mismatch

DESCRIPTION

See how to add unit numbers with AND and OR Criteria where the criteria are given as years and months but date column contains serial number dates. See how to use SUMIFS function to do both AND and OR Criteria:
(01:20) Set up of problem with criteria and data mismatch.
(02:37) Use LOOKUP Function & Big Number lookup_value concept to lookup last number.
(02:37) Create a formula element that will have a duel relative and absolute cell reference when criteria is not listed in each row.
(04:50) Use MONTH function and text month name concatenated with number to trick MONTH to deliver a month number.
(05:41) Use DATE, LOOKUP and MONTH function to create serial number date for the first of each month given text date formula inputs.
(06:10) Use EOMONTH, DATE, LOOKUP and Month to to create serial number date for the end of each month given text date formula inputs.
(07:30) Create Defined Names From Selection with a keyboard: Ctrl + F3.
(08:00) Use SUMIFS with 6 total criteria: 3 AND Criteria and 3 OR Criteria. See how to use SUMIFS with a function argument array operation to enact OR Criteria for adding.
(11:40) See how SUMPRODUCT can add the resultant array of items created by an array formula element without using Ctrl + Shift + Enter

Lots of cool keyboard shortcuts in this video.

WORKBOOKS

Download “Excel Magic Trick 1101-1120” Excel-Magic-Trick-1101-1120.zip – Downloaded 14 times – 2 MB




Be the first to comment

Leave a Reply