SumProduct and Countif

Jesus-LLTK

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.

Sean Morgan

Hello @Jesus-LLTK ,

To count values, with multiple criteria, you could use a COUNTIFS formula. See more on this here: https://help.smartsheet.com/function/countifs

In addition to this, please can you provide a little more detail on your sheet? I am currently unable to identify how you were able to achieve 2 for Building 1 - Customer 1. Is this because "Name 1" appears on either side of customer 1?

Kindest Regards

Sean