IF Count Formula - Won't Count Duplicates

My formula skills are pretty rudimentary so for give my extra-lengthy formula below. I'm sure there is a way to make it cleaner. If so, feel free to let me know. However, that's not my issue here.

Basically, this formula will add a couple columns based off of a name in another tab. If the name matches, a little addition is done and the answer is populated into the cell. My issues is that if the name is duplicated on the 'e-plan' tab, this formula will not work. I don't get an error, I simply get a blank cell.

Any ideas on how to make sure that it calculates duplicate names also?

The file itself is fairly large so I'm hoping the formula alone will give someone enough to work off of.

Please Login or Register  to view this content.

Attach a sample workbook: 20 or so rows will do


Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate.

Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

Ok... so I've uploaded a "spreadsheet" with screen shots. As I stated, this workbook is large. If I try deleting some of it, I get errors everywhere so it's not possible for me to dwindle this thing down to just a small section. It pulls data from an outside source from our home office.

Anyhoo... I think you will be able to see what I'm taking about.

Any ideas on this one?

Let's try this...

Sheet 1 column A has a list of 10 names. Sheet 1 Columns B & C have numbers.

Sheet 2 also has a list of names in column A. Column B will add columns B & C from sheet A if the names match.

The current formula works unless a name is duplicated on Sheet 1. If the name is duplicated, the numbers do not populate on Sheet 2.

I thought pictures might be a stretch. I'll whittle the spreadsheet down today and if there's errors showing everywhere, I'll delete those formulas out. This workbook has so much stuff in it that it'll take a while to delete all of it. It probably has 20 different tabs and about as many macros.

The range is definitely correct because it works with a single name, regardless of which row and there's no way to mess up the spelling, etc. That is selected from a drop down menu.

I'll post a small version today.

Thanks!

Originally Posted by FDibbins

We cannot really work with pics, can you try to mock up a smaller version of what you have? that way, we can offer specific suggestions, instead if just guessing at what you want.

If you vlookup is not returning duplicates, then perhaps your ranges or criteria are wring?

Or, are you sure it is an exact duplicate? check for leading/training spaces and typos

ok... I've deleted everything out and attached it here. Pay no mind to the errors. I put 3 names in the list. Two of them calculate correctly and one of them doesn't. The one that doesn't is because it is duplicated on the reference tab.

Sorry but that one didn't work at all... it produces blank cells.

Originally Posted by joris moerings

Hi,

Change your formula in D9 to this and see if this is the correct result: [FORMULA]=SUMPRODUCT(('e-plan'!H1:H40='City Board with Calculator'!B9)*('e-plan'!I1:J40))[/FORMULA

Joris - My apologies... you are correct. I did my original testing of your formula using my mobile device. My apologies. However, I have since applied it and it does work for except for the rows that contain no data in column B toward the bottom (rows 31 & 32). For some reason, your formula produces an "8" for those particular cells. I'm sure it has something to with the blank cell in column B.

Originally Posted by joris moerings

In addition to FDibbins response: could you explain what's not working at all. The formula created the same result as your lengthy formula. If you took my attached workbook and just copied the formula down, you would need to check the relative reference and make it absolute as FDibbins did in his response.
But even then it would create all 0 value because of the deleted data in E-plan tab of the file.

FDibbins - Your 2nd formula worked for me and solved my original request. I tried using your first formula and the same as above happened. When it got to the bottom couple rows that were blank, it produced weird results in some of the other cells to the right. So, I just left my big ol' long caveman-style formula,... haha

What are you trying to do with the formula in C9?
=IFERROR(IF(COUNTIF('e-plan'!$H$2:$H$33,B9)>=2,INDEX('e-plan'!$G$2:$G$33,MATCH(B9,'e-plan'!$H$2:$H$33,0))+INDEX(OFFSET('e-plan'!$G$1,MATCH(B9,'e-plan'!$H$1:$H$33,0),0):'e-plan'!$G$33,MATCH(B9,OFFSET('e-plan'!$H$1,MATCH(B9,'e-plan'!$H$1:$H$33,0),0):'e-plan'!$H$33,0)),IF(COUNTIF('e-plan'!$H$2:$H$33,B9)=1,(INDEX('e-plan'!$G$2:$G$33,MATCH(B9,'e-plan'!$H$2:$H$33,0))),"")),0)

Almost looks like this would do the same thing?
=SUMIF('e-plan'!$H:$H,$B9,'e-plan'!$G:$G)
(for the main part of that formula)

Then for the next 1, either this...
=SUMPRODUCT(--('e-plan'!$H$1:$H$33=$B9)*'e-plan'!$I$1:$J$33)

Sorry for the delay in replying to you guys... I appreciate the help!