count if function, Index and Match

[Alaska1]

I need to count the number for each product which is in column A but use the column name Product because Product may not be in column A all the time.



I used the countif function but in the product field for I may need to select more than one criteria.

Example:
Unsecured Loans can also include secured in the total.

In column B, I need to calculate for each product the total count in that field.

Product Column B
Unsecured Loans 2.

I tried using the countif function and match, it worked but I could not add more than one criteria. Data sample attached.

[ArviLaanemets]

Have your table as Defined Table. Then you can reference to datarange of any column like:
Tablename[ColumnName], or simply [ColumnName] in case of formula in same Defined Table.

The formulas will be like:

=SUMIFS(TableName[SummedColumnName], TableName[ConditionColumnName1], Condition1, TableName[ConditionColumnName1, Condition2, ...)

To sum with multiply criteria use SUMIFS(). And I advice to use always SUMIFS() and COUNTIFS() instead SUMIF() and COUNTIF(), as they work with any number of criteria.

[Alaska1]

Thank you for your quick response. I created the table and got the Countifs to work to with one product.

When I try with more than one product it is not working. Named the Table February.
=COUNTIFS(February[Product], "Unsecured", February[Product], "Secured")


In column B on worksheet Sheet1, I cannot get the total number for Unsecured and secured

Unsecured and Secured should be counting from Column B in the Data Worksheet.

=SUMIFS(February[Risk Level], February[Product], "Unsecured")

[p45cal]

=COUNTIFS(February[Product], "Unsecured")

or if you're looking to count more than one product, maybe along the lines of:

=SUM(COUNTIFS(February[Product], {"Unsecured","Secured"}))

[ArviLaanemets]

As I don't have Excel available currently, I cant check out your workbook, but SUMIFS() <and SUMIF() too> don't work in way you are trying it!

Based on your posts, something like this may work.

Have worksheets RiskLevels, Report, Products, Months;

On sheet Months, create a Defined Table like tMonths, with 1st column as MonthNo, which is filled numeric values in format YYYY.MM, i.e. 2024.01 for 2024 January, etc. - into as much months into future you think as reasonable. Optionally you can also have there another column MonthName, fillef with matching month names for every MonthNo;

Create a Name lMonths = tMonths[MonthNo]. This can be used as source for Data Validation Lists, whenever you want to select a month (you can't refer to Table columns when defining Data Validation Lists, but you can refer to Names);

On sheet Products, create a Defined Table like tProducts, with 1st column as Product (you can have more columns there, in case you need them);

Create a Name lProducts = tProducts[Product] (again, this is used as source for Data Validation List for selecting products elsewhere in workbook);

On Sheet RiskLevels, create a Defined Table tRiskLevels, with columns like Product, MonthNo, Secured. Define the DataRanges of columns Product and MonthNo as Data Validation Lists for selecting products and month numbers. Define the DataRange of column Secured as Data Validation List with source as 1;0, or True;False, or Secured;Unsecured, or whatever;

In cells on top of Sheet Report, create a Data Validation Lists for selecting month number, and define this cell as Name like nRepMonth;

On same Report sheet, create a Defined Table tReport, with columns like Product, Secured, Unsecured;

Fill the column Product of tReport with your products.

Further depends, what kind of data the column RiskLevel will contain (some numeric or non-numeric value), and what you want the report to return. Basically either count or sum. In case you want to sum (but in this case, there must be some additional column in tRiskLevels - otherwise this table will be somewhat weird with same event having multiple values of same type at same time), the formulas will be like:

For column Secured
=SUMIFS(tRiskLevels[RiskLevel], tRiskLevels[Product], [@Product], tRiskLevels[MontNo];nRepMonth, tRiskLevels[Secured];1)
For column Unecured
=SUMIFS(tRiskLevels[RiskLevel], tRiskLevels[Product], [@Product], tRiskLevels[MontNo];nRepMonth, tRiskLevels[Secured];0)

NB! I used column names without spaces! In case you use spaces there, you must have additional [] around column name in formulas, like tRiskLevels[[Risk Level]]! And having @ preceeding the column name is referring to value in column in same row as the one where formula is placed!

[Alaska1]

Originally Posted by p45cal

=COUNTIFS(February[Product], "Unsecured")

or if you're looking to count more than one product, maybe along the lines of:

=SUM(COUNTIFS(February[Product], {"Unsecured","Secured"}))

Thank you. It worked to count more than one criteria.
=SUM(COUNTIFS(February[Product], {"Unsecured","Secured"}))

I just need to count the product field and then total what is in Risk Level column on data sheet. I need a count based on Product.

[p45cal]

I'm not at all sure how you can total the Risk Level column, so this is counting non-empty cells in that column when the Product column is exactly Secure or Unsecure.

=SUM((February[Product]="Secured")+(February[Product]="Unsecured")*(TRIM(February[[Risk Level ]])<>""))

This one looks for secure in the Product column (which includes Unsecure and Secured LA) and non=empty cells in Risk Level column:

=SUM(ISNUMBER(SEARCH("secure",February[Product]))*(TRIM(February[[Risk Level ]])<>""))

Still a bit of a guess as to what you want.

[Alaska1]

Thank you! I got it to work.

=SUM(COUNTIFS(February[Product], {"Unsecured","Secured"},February[[Risk Level]],"<>"))