Unique/conditional count formula

[smclen]

Hi All,

I'm looking to calculate if there was an upgrade for a supplier, not the total count.

So, for each contract, for each supplier, was there an upgrade (regardless of the count/sum of upgrades)? If the result is yes, then the answer is 1.

In the example above the count of the upgrades is 9, but the answer I am looking to generate is 4.

I am guessing to calculate this I would need a combination of IF, AND, COUNTIF, MAX, but I can't seem to put it together.

Can anyone please help me out?

Thanks!

 

[Michael M]

Assumming your Upgrade Qty is in col "E"

=COUNTIF(E2:E16,1)

 

[smclen]

Assumming your Upgrade Qty is in col "E"

=COUNTIF(E2:E16,1)

Thanks but I didn't explain my question well enough. I'm looking to count for each contract, for each supplier, was there an upgrade (e.g. >0). The exact number is the upgrade qnty column is irrelevant, only that it was >0.

 

[Michael M]

Us e the XL2BB download to post some actual data rather than a picture please
AND I'm guessing a pivottable might do this for you !

 

[smclen]

Sorry I can't download it as I'm using a corporate laptop with strict controls on d/l files.

Hopefully this helps more. Column F is the formula calculation with the result, G is the formula text. I just need to add one more condition to this formula, as it currently does not account for the fact that Selk has no upgrades in row 15 or 16, so I need something that will return a result of 0 in F15 and F16.

 

[Fluff]

How about

=ROWS(UNIQUE(FILTER(A2:B16,D2:D16<>"")))

 

[Fluff]

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Unique/conditional count formula
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.