COUNTIFS and SUBTOTAL Functions Help

Hi

I am trying to figure out how to combine the SUBTOTAL and COUNTIFS functions.

As you can see the COUNTIFS counts the “Y”in column A and “Unknown”in column D.

When I apply the sort feature the COUNTIFS function number remains the same , but the SUBTOAL function number alters based on the sort criteria.

Is my way possible to achieve.

I have attached a copy of the excel spreadsheet.

Hi,
Welcome to this forum..
Tell .. Is that what you were trying to achieve..

Please Login or Register  to view this content.

Check the attached file..

Hi Vikas

Yes... many thanks for your assistance.

You are always welcome...

With an pivot table.

See the attached file.

Hi oeldere

Thanks for your advice I will take a look at this concept.

Originally Posted by Vikas_Gautam

=SUMPRODUCT(SUBTOTAL(2,OFFSET(E11,ROW(A11:A167)-ROW(A11),)),--(A11:A167="Y"),--(D11:D167="Unknown"))

You don't really need a helper column, Vikas. You can use column A (or D) in the OFFSET part because you know those columns must be populated to fulfil the other criteria, i.e.

=SUMPRODUCT(SUBTOTAL(3,OFFSET(A11,ROW(A11:A167)-ROW(A11),)),--(A11:A167="Y"),--(D11:D167="Unknown"))

yeah..
Thanks for the advice.. long..
Actually it was my first time with this type of forumla...

Hi Audere est facere

That function that you provided is exactly what I am looking for. Works like a charm.

Many thanks.

PS I have learnt some thing new.