Problems adding large amounts of data with multiple criteria

Hello All,

I have been stuck with a task at my job for a few days, and have been troubleshooting all over the internet to no avail. The task is as follows:

I must prepare a data snapshot, that totals items from a large data set, using multiple criteria. I have constructed the formula I want to use, and tested the formula on small data range that I created. It works in the mock data set, but will not work with the actual set that I need to use, which contains thousands of items. I constructed the formula's this past Friday, and have been troubleshooting since across the internet with no success. I have quadrupled checked the formula's so I am sure there are no typos.

Basically, I need to provide a count of items that only meet one criteria in the first column, one criteria in the second column, and three criteria in the thrid column. Due to company confidentiality, I have substituted the real criteria with different terms.

Criteria for first column = "Truck" Second column = "Big" Third Column = "Flatbed", or "Crane", or "Tow"

So, I need excel to count any item that meets the above criteria only once - e.g. One count for an item that is a truck, big, and flatbed. One count for an item that is a truck, big, and crane.

Its not about adding the separate columns together, its about only counting items that match the criteria. A big flatbed truck would get counted, as would a big tow truck.

The formula I have put together, and that is working with my test data set (which contains 8 rows) is as follows:

=sum(countifs(A1:A8,"Truck",B1:B8,"Big",C1:C8,{"Flatbed","Crane","Tow"}))

It gives me a correct total of the items in the sample, which is 6, since out of the seven items in that list, one of them has a blank in the column where the search is for an item with criteria "Trucks".

When used on the main data, which is a live sheet with 3265 rows, it returns 0.

Formula for live data page: =sum(countifs(A1:A3265,"Truck",B1:B3265,"Big",C1:C3265,{"Flatbed","Crane","Tow"}))

Are their limitations to countifs? e.g. - it only counts a certain amount of rows, it cannot be used with a live spreadsheet, etc.

I have been looking for a solution, or an alternative for days. Seems like I am chasing my own tail. Pretty much going crazy. Any help is appreciated. Thank you.

No, I'm not aware of a limitation like that.
It's only around 3000 rows, that's not really alot for Excel.

There must be some other reason it doesn't work on the 'real' data.
Check the cells for leading/trailing spaces like " Truck" or "Truck " or " Truck "

Can you post a sample book showing the behavior?

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

Hey Guys,

My apologies for the late response - very busy.

Due to the timeframe I had to complete this work, I ended up coming up with a workaround to this problem - ended up just using hidden pivot tables, that also turn out to be a better option since they will provide for a more dynamic snapshot (less manual work on my end).

Nevertheless, I appreciate the support. Thank you for your help.

- J

Yes, Pivot Tables Rock!
They are sorely underappreciated/underused.