Hello everyone, here is the situation.

I have two sheets in a workbook, each with a list of serial numbers.

A cell on the main sheet compiles the number of times a serial number is entered on either sheet and displays that number next to the serial number.

Here is the formula: =COUNTIFS(sheet2!A:A,A4)+COUNTIF(A:A,A4)

It works as needed. However, the twist now is that I need it to only calculate the number of instances on the condition that the serial number appears at least once on sheet2.


As it functions now:

Example 1 - Serial #:123456 appears 4 times on sheet1 and 3 times on sheet2. The total (7) is listed next to each instance of the serial number.

Example 2 - Serial #:123456 appears 4 times on sheet1 and 0 times on sheet2. The total (4) is listed next to each instance of the serial number.


How I want it to function:

Example 1 - Serial #:123456 appears 4 times on sheet1 and 3 times on sheet2. The total (7) is listed next to each instance of the serial number.

Example 2 - Serial #:123456 appears 4 times on sheet1 and 0 times on sheet2. A blank or zero value is listed next to each instance of the serial number.

Anyone have any ideas? Thanks so much.