countifs in excel 2003

[ysherriff]

I am trying to do a backward compatible countifs for exel. I have used the sumproduct formula but not getting the right result. Can someone direct me to what I may doing wrong?

'=SUMPRODUCT(COL_SECONDS_BIN=$B$5)*(COL_CALL_TYPE=$B15)*(COL_DAY_OF_MONTH=C$14)

Attached is the file. please see tab titled: Trend Chart by Call Type

Thank you for your help.

 

[ysherriff]

Thanks. but i found the solution.

I was missing (-- in my formula....

 

[Hui]

Ysherriff

Are you sure your formula shouldn't be:
=SUMPRODUCT((COL_SECONDS_BIN=$B$5)*(COL_CALL_TYPE=$B15)*(COL_DAY_OF_MONTH=C$14))

 

[ysherriff]

Yes. it is the same as (-- for some reason.

I appreciate it Hui

 

[Hui]

In your formula above the sumproduct() function only applies to the first section in Red below
=SUMPRODUCT(COL_SECONDS_BIN=$B$5)*(COL_CALL_TYPE=$B15)*(COL_DAY_OF_MONTH=C$14)

I'm suggesting maybe it should be applied to all the 3 components
=SUMPRODUCT((COL_SECONDS_BIN=$B$5)*(COL_CALL_TYPE=$B15)*(COL_DAY_OF_MONTH=C$14))

Unless you dropped off a set of brackets in the original formula by mistake when you posted it here

 

[Hui]

Having had a look through your file

1. I'd suggest simplifying the Raw data by removing the calcBin UDF and replacing it with simple formulas on the control sheet. This will speed it up dramatically
You don't need the two columns in Raw Data which show the Minutes and seconds ranges
2. Then use a Pivot Table rather than a Formula table as you were doing
3. Update the Control Sheet to also use formulas instead of UDF Data

See attached spreadsheet that now calculates instantaneously

 

[ysherriff]

Thanks a million Hui. Let me take a look and see what changes you have done. I appreciate it. Will send comments and feedbacks if I have any.

Regards

 

[ysherriff]

Hui, thank you very much. i did not use pivot table because our workforce only have 2003 so i tend to stay away from pivot tables due to incompatible of certain features in later versions. I appreciate the UDF suggestion. Never thought that slow down the file. How did you know it slowed the processing? Curious so I will know for future reference.

Thanks for your help.

 

[ysherriff]

Question Hui? I know you have the minutes bin filtered and the dropdown in pivot table is in ascending order, how can i have it in ascending order for other filter types? for instance seconds bin?

Thanks

 

[ysherriff]

nevermind. i figured it out. thanks Hui

 

[Hui]

The UDF has to run twice for each row of your 3000+ rows
Removing it saved me 1 minute on my pc which is high end
I imagine it saved you a lot more