Multiple matching criteria before summing formula problems

**J Morrow** · 06-07-2011, 04:22 AM

The existing problems that I see with my formulas

1 The SUMIF formula does not work correctly when more than one matching criteria is added.
For example, the formula works okay with one match criteria of column R in the Master sheet "Page". However, if I attempt to have the match criteria for both column R "Page" and column S "Section", the formula will not work. The same applies if I attempt to match both column R "Page" and column P "Base". In the "Stats" tab, cells I2 and J2 work with only one match criteria, all cells highlighted in red are currently a blank as I could not get SIMIF or any other type of formula I could think of to work (columns I,J, Y, Z, AO, AP, BE and BF)

2 Due to the current number of cells containing formulas in the "Stats" tab (will be 12160 cells with formulas); the worksheet will update, save, open and close very slow. I currently have the formula calculation options set to manual. Is there some way to greatly streamline these formulas considering that I know nothing about VBA or macros? The worksheet "Master" currently only contains 2500 rows of data but could grow to 5000 rows.

I have attached a zip file with contains my worksheet and would be very grateful for any help provided.

**TMS** · 06-07-2011, 04:34 AM

I haven't looked at your sample ... just on my way out.

However, one of the common ways to do this in Excel 2003 is with SUMPRODUCT

Pseudo code:

=SUMPRODUCT((Range1=Criteria1)*(Range2=Criteria2)*(Range3)

Where the ranges are for example R2:R300, S2:S300

One way to make it quicker is to use a Worksheet Change event to monitor the input cells and calculate and return values for the corresponding cells rather than just having formulae in them. This is particularly beneficial if you have "pre-populated" hundreds of "empty" cells in preparation for input.

Regards

**J Morrow** · 06-07-2011, 08:46 PM

I am using the SUMPRODUCT formula for the other columns, however, I cannot use this particular formula as there can be multiple data in a given cell of which needs to be counted. For example, a cell could contain: 123 234 345 (notice each set of numbers is seperated by a space and the total count for that cell would be 3).

**TMS** · 06-07-2011, 09:31 PM

Count the spaces and add 1.

For example, =LEN(A1)-LEN(SUBSTITUTE(A1," ",""))+1

Regards

**J Morrow** · 06-08-2011, 12:50 AM

TM Shucks,

Sorry, that suggestion does not work either. When I apply this formula to column J in the Master tab as a check, the result is 1. If I use the following formula in the same column, I receive the correct count of 156. The thing is that I have not been able to get this formula to work when I wish to add 2 or 3 match criteria in columns P, Q and R.

{=SUM(IF(LEN(TRIM(J2:J2500))=0,0,LEN(TRIM(J2:J2500))-LEN(SUBSTITUTE(J2:J2500," ",""))+1))}

Jeff

**Haseeb Avarakkan** · 06-08-2011, 02:13 AM

Hello J Morrow.

If you can use drop down list to choose the category & the sub categories, I think that would speed up the sheet.

I have created a dynamic range. Assume Col_A always be numbers not texts.

If you have more categories add them in the Combo data sheet. also add the appropriate items in Val Data sheet. You can hide these two sheets.

I have deleted some data from the master sheet to reduce the file size to upload. You can paste it from your file.

See the attached.