SumProduct and Countif

I need help trying to count the total matches in two ranges with a criteria. I don't have this problem with Excel using the following formula method.

=SUMPRODUCT(COUNTIFS(C1:C5000,J1:J9,D1:D5000,"Customer 1"))

What i'm doing is using a total of 3 different sheets, 2 are .csv files being uploaded with datauploader using the replace method because the information is constantly changing. While the 3rd sheet is for the purpose of data tracking (Screenshot Below) to put that information elsewhere(ex. dashboard).

With the screenshot above used as an example to hide work related information, I'm trying to replicate what I can achieve with excel by counting how many names from DataUploader2A appear in DataUploader1A with Customer 1. In this case the answer would be "2" for Building 1 - Customer 1.

The same would be counted for "Customer 2" with the answer being 1 for Building 2 - Customer 2.

I could of easily of handled this by having formulas on either Sheet 1 or 2 using Index Match, but because i have to use Replace with DataUploader instead of Merge, the formulas get deleted. Apparently it does not matter if the formulas are in a locked column and hidden.


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!