Countifs

[rrocker1405]

Hi,

I'm trying to use Countifs to count the occurence with multiple criterias matching using either countifs or Sumproduct. Attached is the sheet that I'm using for the same.

The users get to choose values in column L (highlighted in Orange) and based on the selection the counts should be done in Columns o through q (highlighted in Blue) matching the values in the column N (status) and column O (level). I'm able to get the count for 3 criteria's and soon as there more criterions the formula's dont count.

Any suggestions, please.

Thanks in advance.

Regards,
A!

 

[Nebu]

I believe pivot is the best way to go about as you already have so many filters ... Please find the attached

 

[rrocker1405]

Thank you for the quick update. However, I know that this can be resolved using sumproduct or countifs with text string criteria but I'm not sure how to...

thanks in advance. Regards,
A!

 

[Nebu]

Pivot table is basically doing sumproduct for you , it can be resolved using sumproduct formula or Countifs, but I thought if you have an excel build-in function to crunch the data why not take advantage of that..

 

[rrocker1405]

Yes I understand but for reporting purposes, it would be good if I can stay away from pivot table thats why.

Thanks in advance.

 

[Nebu]

Hi:

Here is the file with formula hope this helps...

 

[rrocker1405]

Thank you....works like a charm

Wish you a nice day ahead.

Regards,
A!

 

[Hui]

In O5 you could also use:
=COUNTIFS(Data[Level],O$3,Data[Domain],$L$5,Data[Status],$N5,Data[Project],$L$7,Data[Sprint],$L$8,Data[Vendor],$L$9,Data[Department],$L$10)
Copy across and down

 

[rrocker1405]

Thank you Hui , that really helped me resolve my issue.

 

[rrocker1405]

Hi Hui,

Thank you for the formula. However, the countifs don't calculate for more than 8 criteria for me and gives me a count of zero directly wherease when i use the same with sumproduct it does not count the actual number but atleast it counts with some omissions, not sure why.

the count result per formula is

Countifs =0
sumproduct = 434 wheras it should be 483

I'm using the below formula to calculate data for sumproduct which does an omission on a status and thats the difference in value.

=SUMPRODUCT((DumpTable[Status]=$E7)*(DumpTable[Entity Type]=G$5)*(DumpTable[Level 0]=$C$7)*(DumpTable[IT Domain]=$C$8)*(DumpTable[Project]=$C$10)*(DumpTable[Release]=$C$11)*(DumpTable[Iteration]=$C$12)*(DumpTable[Construction Partner]=$C$13))

=COUNTIFS(DumpTable[Status],E7,DumpTable[Entity],G5,DumpTable[Level 0],C7,DumpTable[IT Domain],C8,DumpTable[Project],C10,DumpTable[Release],C11,DumpTable[Iteration],C12,DumpTable[Construction Partner],C13)

any suggestions, please.

Thanks in advance.

Kind regards,
A!

 

[Nebu]

Can you upload the sample data you are working on...

 

[rrocker1405]

Hi,

Attached is the sample data and you can clearly see the difference in data using sumproduct against the pivot in sheet 4.

Please suggest why is this difference and how do can this be resolved.

many thanks in advance.

Kind regards,
A!

 

[Nebu]

Hi:

There is no error in the formula , its the selection which giving you a value different from the pivot, for e.g: the process level Direct is having corresponding blanks only on the fields Sprint and vendor all other fields have corresponding data so if you try to keep all that as a blank field in your data validation it won't fetch the correct values . In pivot the filter "All" includes al, the fields hence you are getting a different result. Hope I explained it wel...

 

[rrocker1405]

Hi Nebu,

Thank you for the explanation and i know can understand why sumproduct is not picking some values. However, if I would like to include 'all' like in the pivot table, what should be done? could you suggest, please.

Thanks in advance.
kind regards,
A!

 

[Nebu]

Sumproduct can can only compare multiple columns, but not the values with in a column . I guess you can use a pivot table and use get pivot data formula by making the references in this formula dynamic, but u will have to write a small macro to refresh the pivot tables while changing your data validation cells...