Hi All,
Count formula's with Duplicate values are not my friend! Please would someone assist with my query below.
What I am wanting to do:
Count Duplicates in Column A once as long as there is a number higher than 0 in Column B
Column A Column B Apples - Oranges 1 Pears 0 Apples 1 Oranges 2 Apples 1
ie. The Answer should be:
Apples - 1
Oranges - 1
Pears - 0
Please let me know if anything is unclear.
Thank you for your help!
hi there again. so no matter how many times the fruit appears with 1 in column B, it will be "1"? otherwise, "0"? assuming data in A2:B7, try:
=IF(COUNTIFS($A$2:$A$7,"Apples",$B$2:$B$7,">0"),1,0)
changing the one in red to a cell reference would work too. for eg, if you have "Apples" in E2, then:
=IF(COUNTIFS($A$2:$A$7,E2,$B$2:$B$7,">0"),1,0)
Thanks, if you have clicked on the * and added our rep.
If you're satisfied with the answer, click Thread Tools above your first post, select "Mark your thread as Solved".
"Contentment is not the fulfillment of what you want, but the realization of what you already have."
Tips & Tutorials I Compiled | How to Get Quick & Good Answers
Hi benishiryo,
Thank you again for helping!
This worked for my simple example spreadsheet, hence the quick SOLVED, but I've realised I haven't been clear enough as to what I am using it for.
Are you able to take a look at the attached, and let me know if there is a way to count the unique values in A with values higher than 0 in column B.
Thank you!
maybe this array formula in B16:
Formula:
Please Login or Register to view this content.
or to handle blanks:
Formula:Please Login or Register to view this content.
you could also actually use this non-array formula for B15:
=SUMPRODUCT((PM<>"")/COUNTIF(PM,PM&""))
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks