Anyone have a quick fix for SUMIFonlycountonce.xlsx
Looking to count ordered units based on a number as Parent or Customer, but not to double the order if the number happens to be both Parent and Customer. Trying to get to 40 in one formula with this example so I don't have to use a helper for 200+ parents/customers.
=sumproduct(((a2:a5=100)+(c2:c5=100)>0)*e2:e5)
Samba
Say thanks to those who have helped you by clicking Add Reputation star.
Not clear what you want to do about the 101s. I included them. I had to extend the range with this; the SUM formula in F6 will have to be moved down.With the numbers arranged as in your upload the total = 40. With C4 and C5 swapped it still returns 40. Is this what you expect?Formula:
Please Login or Register to view this content.
Dave
Here's another one...
=SUMPRODUCT(SIGN((A2:A5=100)+(C2:C5=100)),E2:E5)
Biff
Microsoft MVP Excel
Keep It Simple Stupid
Let's Go Pens. We Want The Cup.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks