Additional criteria for 'sumproduct' formula

Hello, Thank you in advance for your help. I belive this is probably a simple question for someone with expert-level Excel knowledge, so thanks for taking the time to help.

I have an Excel workbook that is doing several calculations. Right now I have 2 tabs within the book that I am focusing on fixing a formula.

TabA is a summarative sheet, in that I have the formula used frequently:
=SUMPRODUCT((Comparison!F$1:F$17889="X")*(Comparison!$A$1:$A$17889=$A7)

Comparison! is the 2nd tab, which lists many thousands of rows of assets held in different cities
Col A = shows the names of the city (in both tabs - TabA it shows it in the summarative table, and in Comparison! it shows it as the city name for each asset)
Col B = has an X if the data has changed since our last report (based on another spreadsheet, but that is unimportant to the question)

You can see in my formula, there are 17889 rows of assets in the tab. However, there is actually about 20000, but I specifically put 17889 because i need to differntiate between old assets and new ones (rows 1-17889 are old assets, rows 17890 to 20000 are new assets. New assets are always listed at the bottom). So we have 17889 old assets, and then the rest are new. I know this because I manually looked at the spreadsheet. Each time I run the report, I have to manually do this, and then do a formula replace, replacing the 17889 with whatever the new number is of "old assets", so that I exclude new assets.

However, this is where I'd like to fix the formula, so I can do this without manually having to do anything, or ever having to change the formula. And the way I could do this is to add in a criteria that: if col B = the letter "X", then it should not count.

So ideally the formaul would end up being something like:
=SUMPRODUCT((Comparison!F$1:F$20000="X")*(Comparison!$A$1:$A$20000=$A7)-when B$1:B$200000="X"

But of course the "-when" would be replaced by something else. And also, with order of operations issues, I'm sure its not even written like this anyways.

Hopefully I'm clear in what I want. If need be, I could post the spreadsheet.
Thanks very much!
Dave

You're so close.

Someone more thoughtful than me will post a good link to an explanation of this kind of formula so you can work it out for yourself in future...

=SUMPRODUCT((Comparison!F$1:F$20000="X")*(Comparison!$A$1:$A$20000=$A7)*(B$1:B$200000<>"X"))

HTH

Hi,

Will this

Please Login or Register  to view this content.

do it for you?

It looks like CC's will work, but he forgot to reference the Sheetname too in the last argument...

so should be:

=SUMPRODUCT((Comparison!C$1:C$20000="X")*(Comparison!$A$1:$A$20000=$B6)*(Comparison!B$1:B$20000<>"X"))