Sum Product ( Count if working good, but I need help with dragging formula.

After my last post a couple of days ago and talking with one of the experts on here, I thought I could figure this one out.

I have a number line 0-9 in top row H1 through Q1

Then I have a set of 3 digit numbers G6 through G65 (These numbers will continue)
Also these numbers are pulled from date in column A and numbers from Column D

Below the number line in row 24 you will see 1 1 3 3 0 3 1 4 2 3
these numbers represent how many times each of the last 7, 3 digit numbers in column G appear.

I have this formula in place for that row =SUMPRODUCT(COUNTIF(H$1,MID($G6:$G24,{1,2,3},1)))

The problem is where column G does not go row after row every time, but sometimes it does for 1 row, it will not calculate the rest of the rows for me.

You can see an example in row 25 under Columns H1 through Q1, it tries to look at G7 for next number instead of G9 through G25

Thanks for the help,
Brian

See attached file. I used helper column R. Formulas in H24 to Q24 use reference to column R.
Drag these formulas down and it works.

Create a list of your numbers without variable gaps - see attached.

Sorry, I should have explained a little better.
Where the formula goes wrong is when I drag down
I understand both of your suggestions will work, but the problem is
I need to keep the layout the same when I drag it to copy.

I'm fine with the helper column, when I was trying to figure it out I also
Had a helper column

I Thought I could upload an updated sheet, but I'm on my phone and it won't let me.
If you take and drag the row all the way down that is in red font from my original spreadsheet you will see where the problem is happening. After dragging down, go back
And click on one of the red numbers in the H-Q columns.
You will see that it isn't pulling data from the correct place.


Thanks,
I hope this helps
Sorry for the confusion

I don't understand the issue. If you look at the file I attached in msg#2, format up to column Q is same as your original file. If you hide column R, everything looks the same and data in columns H thru Q is correct. Problem with column G data is that the gaps are varying. So column R puts data in consecutive rows and that makes the formulas in H:Q work better. In your last message you talk about dragging formulas in your original message but those formulas refer to column G. So that's why they don't work.

Modytrane,

Your formula did work. I would've used it, but i knew it was possible for the way I wanted (Not consecutive)

Im not sure about the formulas working better if the rows are consecutive. I'm still very new to excel formulas. Im trying to learn everything I can.

One of the great things about excel is, a person can have almost any layout they want regardless of looks and make the formulas work, which is what I'm trying to do.

Again, I do greatly appreciate you giving me something better than what i had, because mine did not work at all.

Thanks,
Brian

John,

Your formulas worked exactly as I needed it.

Thanks for taking the time to help with my issue.