Is there any Alternate formula for Sumproduct function?

Hello Experts,

Please help me in calculating the count of multiple criteria filtered details into count.i used sumproduct function to display the count of multiple criteria filtered data and showing the correct counts as well.However it is taking few seconds to evaluate and produce the output for the 3000+ line item of data.

Instead is there an formula that can produce the same output result what sumproduct does.Please find the exhibit which am producing output using sumproduct function.

Sumproduct counts.JPG

You can use COUNTIFS. Or, maybe try using a Pivot Table.

Thanks for the rep.


Your profile says you use Excel 2013. You didn't say anything about Excel 2003. Who knew?

Use a Pivot Table: https://www.techonthenet.com/excel/pivottbls/create.php

Hello Pete,
Really appreciate your valuable time spent on the query i raised

Formula:

Please Login or Register  to view this content.

It's absolutely producing the same output what it was showing for sumproduct function.Let me try it in the 3000+ data line summary and let you know.

Suggest you post a sample workbook with some typical data. Wouldn't have thought 3000 rows would be an issue. Suspect there may be something else causing the delay.

Edit Forget this one. I overlooked the 2003 requirement. It won't take whole rows.

Color me skeptical. My timer indicates this is faster than SUMPRODUCT. I don't know what it will do with larger data.

Formula:

Please Login or Register  to view this content.

Originally Posted by pramoth.u

Hello TMS,
Thanks for your value time.
My version of excel is 2013 only and countifs will verymuch supported and knew how to use it as well.
However the reports which i shares to few user are still using the lower version of the package.
Has i quoted before the line item will be more than 3000+ hence it's not viable to use pivot table and there might be a chance of sending the summary without refreshing it.
I am looking for an automated count display using formula rather than pivot.

Please help here

Why?

A Pivot table doesn't mind how many rows it analyses and where the underlying data range is large it will be much faster and it avoids the slowness that you'll inevitably get with functions, particularly array formulae.

The PT can be made to refresh automatically hence avoiding it being out of date. You should always apply a dynamic range name to your data so that the range name automatically expands and contracts as data is added or removed, and the PT should use the dynamic range name as the Source Range.

Then in the PT sheet Activate event in VBA just add a single line of code which refreshes the PT cache whenever the PT sheet is selected.