Subtotal of one column based on the value of two other columns

[J.Badman]

Hello,

I have a table of information which is exported out of a CRM programme. In the first column is the users name, the second column is a value which includes a subtotal for the user in the third column there is a letter (either C or L).

I have managed to do a lookup to get the total for the user excluding the subtotal by using this formula. (WBT being the exported worksheet, column C is the value column, column B is their names. The DOC being the sheet the output is then displayed on).

=INDEX(WBT!C:C,SUMPRODUCT(MAX(ROW(WBT!B:B)*(DOC!A9=WBT!B:B))))

I now need to get the same result, only subtotalling the data in column B (in my example attached) based on the data in column C and excluding the subtotal that the CRM software has already calculated.

So, for example, Where Column C has a C, I need the total of these . In this instance, John would be 8, Steve 17 and Mark would be 16.

I have tried adding an IF argument to the above formula but does not like it. Any help gratefully received.

Thanks

[Mrrrr]

You could use a helper column to tell you which cell in B1:B26 contains a formula and which doesn't, eg in column D and drag down:
=IF(ISFORMULA($B$1:$B$26)=FALSE;1;0)

Then in E1:
=SUMIFS(B1:B26;A1:A26;"John";C1:C26;"C";D1:D26;1)

[J.Badman]

Unfortunately when the data is exported and copied into the worksheet, there is no formula, it is just a value.

[Mrrrr]

Oh, ok.

If the data comes as in the example file except for the subtotals' formulas, you could add the following formula to D column and drag down to get 1 and 0, similarly as in my previous post:
=IF(AND(B1<>"";B2<>"");1;0)

With the formula above it shows 1 next to all cells except the subtotal cell and the empty cell following it.

Then in E1 you can add the formula I posted before and there's the result.

Maybe this can be done in another more professional way I can't think of.