# Tags: SUMIFS

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 08-03-2018

The objective is the create another 3 column dataset with the following conditions:

1. If the patient's latest smoking status is other than Blank or 9, then consider that as the smoking status of the patient; and
2. If the patient's latest smoking status is blank or 9, then consider the previous smoking status that is not blank or 9; and
3. If the patient's smoking status is blank or 9 on all dates, then consider the smoking status as 9

The expected result is:

 PatientID Last date when the smoking status was other than 9 or Blank Smoking status on that date P1 08-Mar-18 1 P2 10-Mar-18 9 P3 10-Mar-18 2 P4 09-Mar-18 1

I have solved this question using 3 methods - PowerPivot, Advanced Filters and formulas.  You may download my solution workbook from here.

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 should contain either b or c or d

So the result for the dataset above should be 6 i.e. 1+5.  Sounding like a simple case of summarising based on multiple conditions, one may attempt using the SUMIFS() function here.  However, a single SUMIFS() function will not work because a SUMIFS() can only accept multiple AND conditions (not OR conditions).  The only way to make the SUMIFS() function work is to use 3 of them and then add them up.

So this SUMIFS() function will work

=SUMIFS(\$C\$3:\$C\$7,\$A\$3:\$A\$7,A11,\$B\$3:\$B\$7,B11)+SUMIFS(\$C\$3:\$C\$7,\$A\$3:\$A\$7,A12,\$B\$3:\$B\$7,B12)+SUMIFS(\$C\$3:\$C\$7,\$A\$3:\$A\$7,A13,\$B\$3:\$B\$7,B13)

A11:A13 has Active and B11:B13 has *a*,*b*,*c*.

While this looks like a solution, the problem is that there will have to be one SUMIFS() function for each OR condition.  So the more the number of OR conditions, the more the SUMIFS() functions and the lengthier the formula.

In this workbook, I have shared two alternative solution.  One uses the DSUM() function and other uses a combination of MMULT(), TRANSPOSE() and SUMPRODUCT() functions.