drhamel69

asked on

# EXCEL COUNT OR SUM IF WITH DUPLICATES PROBLEM??

I have an issue that I don't know is possible to do without doing it by hand. Please open the excel file before proceeding.

I have a list of parts that are classified by component. The fields BXZ,ROAD,STUNT,CRUISER are boolean not actual part counts. They are 1 if that part is needed for that component for that model or 0 if not. Some parts are multiuse.

To the right I have a "complexity matrix". I can use sumif and count if to get total part complexity and permodel complexity. the problem comes when I need to bucket to models together. I can not just add them because of the common parts. I have included the correct answer just by counting them and typing it in myself.

What I need is a forumula/method for figuring cells q3 and r3 out correctly without doing it by hand.

Any thoughts?

ComplexityExample.xlsx

I have a list of parts that are classified by component. The fields BXZ,ROAD,STUNT,CRUISER are boolean not actual part counts. They are 1 if that part is needed for that component for that model or 0 if not. Some parts are multiuse.

To the right I have a "complexity matrix". I can use sumif and count if to get total part complexity and permodel complexity. the problem comes when I need to bucket to models together. I can not just add them because of the common parts. I have included the correct answer just by counting them and typing it in myself.

What I need is a forumula/method for figuring cells q3 and r3 out correctly without doing it by hand.

Any thoughts?

ComplexityExample.xlsx

Should it be 5 rather than 4 for WHEEL for BMX & STUNT?

If so, then:

=SUMPRODUCT(($A$3:$A$14=J3)*((D3:D14+F3:F14)>0))

=SUMPRODUCT(($A$3:$A$14=J3

ASKER

I am sorry you are correct. The other thing I forgot to put is it needs to be vlookup style. I mean I will have a list of 100 components of varying part numbers. So I can't reference cells directly.

I'm not sure what you mean by "vlookup style". Can you clarify?

ASKER CERTIFIED SOLUTION

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds.

**No credit card required.**ASKER

This actually works but can you explain how? Sorry but it has just driven me nuts.

ASKER

I am sorry I mean rorya's works.

It checks if each item in col A matches J3 (WHEEL) and returns either True or False.

It then checks if adding up the values in col D and F is more than 0 and returns True or False

These two arrays of True/False values are then multiplied together (in Excel, TRUE=1, FALSE=0) to give a final array of 1s and 0s and then adds these up.

Does that make sense?

It then checks if adding up the values in col D and F is more than 0 and returns True or False

These two arrays of True/False values are then multiplied together (in Excel, TRUE=1, FALSE=0) to give a final array of 1s and 0s and then adds these up.

Does that make sense?