Sumifs with the addition of a new constraint

Excel Gods,

I am having trouble with my sumifs formula. I added a new constraint so my formula isn’t coming out exactly with the totals it should be.

My worksheet called DP4Q2013 is exactly the same as worksheet 4Q2013Incurred except for the fact I added two new columns M:N to my table in worksheet DP4Q2013.

The problem I am having is with columns AB:AG of worksheet DP4Q2013.

The formula I have in 4Q2013Incurred is

Please Login or Register  to view this content.

The new formula I have in

Please Login or Register  to view this content.

The problem with my new formula is it doesn’t account for a few sum ranges that do not have N/A at the end of it. Some sumranges have A03, A07, A51, etc. at the end instead of N/A so my formula doesn’t sum it all of and therefore for my totals don’t match.

Help would be greatly appreciated and thank you!
ExcelHelp.xlsx

Thanks for your help AlKey, but that isn't exactly what I had wanted.

Column AB:AG's headers are UW_G, UW_N, and UW_O. What I wanted to do is know the premiums and claims based on those three writing classes (G,N,O). Those letters can be found in column P, 5 text from the right.

If you look at worktab 4Q2013Incurred, that was exactly what I did. But I added in a new constraint so now it is messing with my formula, and I do not know how to fix it.

Originally Posted by FDibbins

Will this work for you?
=SUMIFS($S$4:$S$178,$P$4:$P$178,$X4&"_"&RIGHT(AB$2,)&"*")

Thanks for your reply FDibbins, but that formula didn't work. It gave me the same values as the formula Alkey gave me.
The values that are being given is the sum of all column S. I need the sums to be split in three based on if they are G, N, or O.

Thanks for your reply FDibbins, but that formula didn't work. It gave me the same values as the formula Alkey gave me.
The values that are being given is the sum of all column S. I need the sums to be split in three based on if they are G, N, or O.

Essentially, Columns AB, AD, and AF are the same. They are looking for the sums of Column S, rec prem, based on if it is a G, N, or O.

Columns AC, AE, and AG are the same. They are looking for the sums of Column T, claims, based on if it is a G, N, or O.

Originally Posted by Richard Buttrey

Can't you just add the A07 records to your list so that the SUMIF does pick them up?

Have you thought about using a Pivot Table which is usually the first port of call for this sort of stuff.

I am not sure exactly what you mean by added A07 to my records, and a pivot table will not help with what I am trying to accomplish.

I just figured it out. The formula I used was

Please Login or Register  to view this content.

I sorta incorporated what everyone said, so thank you all! Reputations all around.