SUMIFS and Excel 2003

[BKNewton29]

Dear All,

I've created a spreadsheet with numerous SUMIFS formulas (1,475 to be exact)

When I rolled it out to the rest of the company it was soon discovered that they had Excel 2003 not Excel 2007 like myself. Consequently it doesn't work.

My desire to change 1,475 cells to a sumproduct formula is non existent to say the least.

Does anyone know of a way to create a SUMIFS function in Excel 2003 with VBA code to get around this?

thanks

 

[Trevor G]

Have you looked at SUMPRODUCT

=SUMPRODUCT((A1:A20=1000)*(B1:B20=2000)*(C1:C20))

If possible show sample of your spreadsheet in this thread, you can copy and paste it

 

[BKNewton29]

<TABLE style="WIDTH: 122pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=162><COLGROUP><COL style="WIDTH: 122pt; mso-width-source: userset; mso-width-alt: 2962" width=162><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 122pt; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl77 height=20 width=162>Example of a few of the 1475 cells below. As you can see converting them all to sumproduct would take an eternity

=-SUMIFS(INDIRECT('Data Tab'!$K$1),'Line 100 data'!$B$2:$B$10000,1)-INDIRECT('Data Tab'!L6&6)



</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl77 height=20>=-SUMIFS(INDIRECT('Data Tab'!$K$1),'Line 100 data'!$B$2:$B$10000,5)-INDIRECT('Data Tab'!L6&7)


</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl77 height=20>=-SUMIFS(INDIRECT('Data Tab'!$K$1),'Line 100 data'!$B$2:$B$10000,4)-INDIRECT('Data Tab'!L6&8)


</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl77 height=20>=-SUMIFS(INDIRECT('Data Tab'!$K$1),'Line 100 data'!$B$2:$B$10000,10)-INDIRECT('Data Tab'!L6&15)


</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl77 height=20>=-SUMIFS(INDIRECT('Data Tab'!$K$1),'Line 100 data'!$B$2:$B$10000,7)-INDIRECT('Data Tab'!L6&10)


</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl77 height=20>=-SUMIFS(INDIRECT('Data Tab'!$K$1),'Line 100 data'!$B$2:$B$10000,6)-INDIRECT('Data Tab'!L6&9)


</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl77 height=20>=-SUMIFS(INDIRECT('Data Tab'!$K$1),'Line 100 data'!$B$2:$B$10000,13)-INDIRECT('Data Tab'!L6&11)


</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl77 height=20>=-SUMIFS(INDIRECT('Data Tab'!$K$1),'Line 100 data'!$B$2:$B$10000,3)-INDIRECT('Data Tab'!L6&12)


</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl77 height=20>=-SUMIFS(INDIRECT('Data Tab'!$K$1),'Line 100 data'!$B$2:$B$10000,9)-INDIRECT('Data Tab'!L6&13)


</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl77 height=20>=-SUMIFS(INDIRECT('Data Tab'!$K$1),'Line 100 data'!$B$2:$B$10000,11)-INDIRECT('Data Tab'!L6&14)


</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl77 height=20>=-SUMIFS(INDIRECT('Data Tab'!$K$1),'Line 100 data'!$B$2:$B$10000,15)-SUMIFS(INDIRECT('Data Tab'!$K$1),'Line 100 data'!$B$2:$B$10000,90)-INDIRECT('Data Tab'!L6&16)</TD></TR></TBODY></TABLE>