Tags: SUMIFS

Determine the most recent status after satisfying certain conditions

{ 0 Comments }

By Ashish Mathur · April 29, 2018 · CALCULATION, FILTERS, POWERPIVOT · Leave a comment

Assume a three column dataset with Patient ID, Smoking Status and Review Date PatientID SmokingStatus ReviewDate P1 10-03-2018 P1 9 09-03-2018 P1 1 08-03-2018 P1 4 07-03-2018 P2 9 10-03-2018 P2 9 09-03-2018 P2 9 08-03-2018 P2 9 07-03-2018 P3 2 10-03-2018 P3 09-03-2018 P3 9 08-03-2018 P4 9 10-03-2018 P4 1 09-03-2018 P4 4 […]

Summarise data with multiple wildcard OR conditions

{ 0 Comments }

By Ashish Mathur · November 7, 2015 · DATA SUMMARISING · Leave a comment

Imagine a dataset as shown below Status Text Number Active bat 1 Passive erq 2 Passive cat 3 Active enm 4 Active dog 5 Suppose the data is in range A3:C7 (headings are in A2:C2). The objective is to add numbers where the following conditions are met: 1. Status should be Active; and 2. Text […]