multiple criteria sumifs or array

S

sobersky

New Member
Joined
Sep 8, 2011
Messages
3
<TABLE style="WIDTH: 635pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=844><COLGROUP><COL style="WIDTH: 72pt; mso-width-source: userset; mso-width-alt: 3510" span=2 width=96><COL style="WIDTH: 53pt; mso-width-source: userset; mso-width-alt: 2560" width=70><COL style="WIDTH: 56pt; mso-width-source: userset; mso-width-alt: 2742" width=75><COL style="WIDTH: 53pt; mso-width-source: userset; mso-width-alt: 2560" width=70><COL style="WIDTH: 53pt; mso-width-source: userset; mso-width-alt: 2560" width=70><COL style="WIDTH: 56pt; mso-width-source: userset; mso-width-alt: 2742" width=75><COL style="WIDTH: 53pt; mso-width-source: userset; mso-width-alt: 2560" width=70><COL style="WIDTH: 53pt; mso-width-source: userset; mso-width-alt: 2560" width=70><COL style="WIDTH: 56pt; mso-width-source: userset; mso-width-alt: 2742" width=75><COL style="WIDTH: 58pt; mso-width-source: userset; mso-width-alt: 2816" width=77><TBODY><TR style="HEIGHT: 38.25pt" height=51><TD style="BORDER-BOTTOM: gray 0.5pt solid; BORDER-LEFT: gray 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 72pt; HEIGHT: 38.25pt; BORDER-TOP: gray 1pt solid; BORDER-RIGHT: #f0f0f0" class=xl81 height=51 width=96>Total</TD><TD style="BORDER-BOTTOM: gray 0.5pt solid; BORDER-LEFT: gray 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 72pt; BORDER-TOP: gray 1pt solid; BORDER-RIGHT: gray 0.5pt solid" class=xl82 width=96>Amount Deposited</TD><TD style="BORDER-BOTTOM: gray 0.5pt solid; BORDER-LEFT: gray; BACKGROUND-COLOR: transparent; WIDTH: 53pt; BORDER-TOP: gray 1pt solid; BORDER-RIGHT: gray 0.5pt solid" class=xl78 width=70>Chq
Status1</TD><TD style="BORDER-BOTTOM: gray 0.5pt solid; BORDER-LEFT: gray; BACKGROUND-COLOR: transparent; WIDTH: 56pt; BORDER-TOP: gray 1pt solid; BORDER-RIGHT: gray 0.5pt solid" class=xl79 width=75>Amount</TD><TD style="BORDER-BOTTOM: gray 0.5pt solid; BORDER-LEFT: gray; BACKGROUND-COLOR: transparent; WIDTH: 53pt; BORDER-TOP: gray 1pt solid; BORDER-RIGHT: gray 1pt solid" class=xl80 width=70>Date
Deposited</TD><TD style="BORDER-BOTTOM: gray 0.5pt solid; BORDER-LEFT: gray; BACKGROUND-COLOR: transparent; WIDTH: 53pt; BORDER-TOP: gray 1pt solid; BORDER-RIGHT: gray 0.5pt solid" class=xl78 width=70>Chq
Status2</TD><TD style="BORDER-BOTTOM: gray 0.5pt solid; BORDER-LEFT: gray; BACKGROUND-COLOR: transparent; WIDTH: 56pt; BORDER-TOP: gray 1pt solid; BORDER-RIGHT: gray 0.5pt solid" class=xl79 width=75>Amount</TD><TD style="BORDER-BOTTOM: gray 0.5pt solid; BORDER-LEFT: gray; BACKGROUND-COLOR: transparent; WIDTH: 53pt; BORDER-TOP: gray 1pt solid; BORDER-RIGHT: gray 1pt solid" class=xl80 width=70>Date
Deposited</TD><TD style="BORDER-BOTTOM: gray 0.5pt solid; BORDER-LEFT: gray; BACKGROUND-COLOR: transparent; WIDTH: 53pt; BORDER-TOP: gray 1pt solid; BORDER-RIGHT: gray 0.5pt solid" class=xl78 width=70>Chq
Status3</TD><TD style="BORDER-BOTTOM: gray 0.5pt solid; BORDER-LEFT: gray; BACKGROUND-COLOR: transparent; WIDTH: 56pt; BORDER-TOP: gray 1pt solid; BORDER-RIGHT: gray 0.5pt solid" class=xl79 width=75>Amount</TD><TD style="BORDER-BOTTOM: gray 0.5pt solid; BORDER-LEFT: gray; BACKGROUND-COLOR: transparent; WIDTH: 58pt; BORDER-TOP: gray 1pt solid; BORDER-RIGHT: gray 1pt solid" class=xl80 width=77>Date
Deposited</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: gray 0.5pt solid; BORDER-LEFT: gray 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND: #ffffcc; HEIGHT: 15pt; COLOR: windowtext; FONT-SIZE: 10pt; BORDER-TOP: gray 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: #f0f0f0; TEXT-DECORATION: none; mso-pattern: black none; text-underline-style: none; text-line-through: none" class=xl91 height=20> </TD><TD style="BORDER-BOTTOM: gray 0.5pt solid; BORDER-LEFT: gray 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND: #ffffcc; COLOR: windowtext; FONT-SIZE: 10pt; BORDER-TOP: gray 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: gray 0.5pt solid; TEXT-DECORATION: none; mso-pattern: black none; text-underline-style: none; text-line-through: none" class=xl92> </TD><TD style="BORDER-BOTTOM: gray 0.5pt solid; BORDER-LEFT: gray 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND: #ffffcc; COLOR: windowtext; FONT-SIZE: 10pt; BORDER-TOP: gray 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: gray 0.5pt solid; TEXT-DECORATION: none; mso-pattern: black none; text-underline-style: none; text-line-through: none" class=xl96> </TD><TD style="BORDER-BOTTOM: gray 0.5pt solid; BORDER-LEFT: gray 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND: #ffffcc; COLOR: windowtext; FONT-SIZE: 10pt; BORDER-TOP: gray 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: gray 0.5pt solid; TEXT-DECORATION: none; mso-pattern: black none; text-underline-style: none; text-line-through: none" class=xl89> </TD><TD style="BORDER-BOTTOM: gray 0.5pt solid; BORDER-LEFT: gray 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND: #ffffcc; COLOR: windowtext; FONT-SIZE: 10pt; BORDER-TOP: gray 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: gray 1pt solid; TEXT-DECORATION: none; mso-pattern: black none; text-underline-style: none; text-line-through: none" class=xl90> </TD><TD style="BORDER-BOTTOM: gray 0.5pt solid; BORDER-LEFT: gray 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND: #ffffcc; COLOR: windowtext; FONT-SIZE: 10pt; BORDER-TOP: gray 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: gray 0.5pt solid; TEXT-DECORATION: none; mso-pattern: black none; text-underline-style: none; text-line-through: none" class=xl96> </TD><TD style="BORDER-BOTTOM: gray 0.5pt solid; BORDER-LEFT: gray 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND: #ffffcc; COLOR: windowtext; FONT-SIZE: 10pt; BORDER-TOP: gray 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: gray 0.5pt solid; TEXT-DECORATION: none; mso-pattern: black none; text-underline-style: none; text-line-through: none" class=xl89> </TD><TD style="BORDER-BOTTOM: gray 0.5pt solid; BORDER-LEFT: gray 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND: #ffffcc; COLOR: windowtext; FONT-SIZE: 10pt; BORDER-TOP: gray 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: gray 1pt solid; TEXT-DECORATION: none; mso-pattern: black none; text-underline-style: none; text-line-through: none" class=xl90> </TD><TD style="BORDER-BOTTOM: gray 0.5pt solid; BORDER-LEFT: gray 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND: #ffffcc; COLOR: windowtext; FONT-SIZE: 10pt; BORDER-TOP: gray 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: gray 0.5pt solid; TEXT-DECORATION: none; mso-pattern: black none; text-underline-style: none; text-line-through: none" class=xl96> </TD><TD style="BORDER-BOTTOM: gray 0.5pt solid; BORDER-LEFT: gray 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND: #ffffcc; COLOR: windowtext; FONT-SIZE: 10pt; BORDER-TOP: gray 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: gray 0.5pt solid; TEXT-DECORATION: none; mso-pattern: black none; text-underline-style: none; text-line-through: none" class=xl89> </TD><TD style="BORDER-BOTTOM: gray 0.5pt solid; BORDER-LEFT: gray 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND: #ffffcc; COLOR: windowtext; FONT-SIZE: 10pt; BORDER-TOP: gray 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: gray 1pt solid; TEXT-DECORATION: none; mso-pattern: black none; text-underline-style: none; text-line-through: none" class=xl90> </TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: gray 0.5pt solid; BORDER-LEFT: gray 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND: #ffffcc; HEIGHT: 15pt; COLOR: windowtext; FONT-SIZE: 10pt; BORDER-TOP: gray 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: #f0f0f0; TEXT-DECORATION: none; mso-pattern: black none; text-underline-style: none; text-line-through: none" class=xl91 height=20> </TD><TD style="BORDER-BOTTOM: gray 0.5pt solid; BORDER-LEFT: gray 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND: #ffffcc; COLOR: windowtext; FONT-SIZE: 10pt; BORDER-TOP: gray 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: gray 0.5pt solid; TEXT-DECORATION: none; mso-pattern: black none; text-underline-style: none; text-line-through: none" class=xl92> </TD><TD style="BORDER-BOTTOM: gray 0.5pt solid; BORDER-LEFT: gray 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND: #ffffcc; COLOR: windowtext; FONT-SIZE: 10pt; BORDER-TOP: gray 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: gray 0.5pt solid; TEXT-DECORATION: none; mso-pattern: black none; text-underline-style: none; text-line-through: none" class=xl96> </TD><TD style="BORDER-BOTTOM: gray 0.5pt solid; BORDER-LEFT: gray 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND: #ffffcc; COLOR: windowtext; FONT-SIZE: 10pt; BORDER-TOP: gray 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: gray 0.5pt solid; TEXT-DECORATION: none; mso-pattern: black none; text-underline-style: none; text-line-through: none" class=xl89> </TD><TD style="BORDER-BOTTOM: gray 0.5pt solid; BORDER-LEFT: gray 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND: #ffffcc; COLOR: windowtext; FONT-SIZE: 10pt; BORDER-TOP: gray 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: gray 1pt solid; TEXT-DECORATION: none; mso-pattern: black none; text-underline-style: none; text-line-through: none" class=xl90> </TD><TD style="BORDER-BOTTOM: gray 0.5pt solid; BORDER-LEFT: gray 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND: #ffffcc; COLOR: windowtext; FONT-SIZE: 10pt; BORDER-TOP: gray 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: gray 0.5pt solid; TEXT-DECORATION: none; mso-pattern: black none; text-underline-style: none; text-line-through: none" class=xl96> </TD><TD style="BORDER-BOTTOM: gray 0.5pt solid; BORDER-LEFT: gray 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND: #ffffcc; COLOR: windowtext; FONT-SIZE: 10pt; BORDER-TOP: gray 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: gray 0.5pt solid; TEXT-DECORATION: none; mso-pattern: black none; text-underline-style: none; text-line-through: none" class=xl89> </TD><TD style="BORDER-BOTTOM: gray 0.5pt solid; BORDER-LEFT: gray 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND: #ffffcc; COLOR: windowtext; FONT-SIZE: 10pt; BORDER-TOP: gray 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: gray 1pt solid; TEXT-DECORATION: none; mso-pattern: black none; text-underline-style: none; text-line-through: none" class=xl90> </TD><TD style="BORDER-BOTTOM: gray 0.5pt solid; BORDER-LEFT: gray 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND: #ffffcc; COLOR: windowtext; FONT-SIZE: 10pt; BORDER-TOP: gray 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: gray 0.5pt solid; TEXT-DECORATION: none; mso-pattern: black none; text-underline-style: none; text-line-through: none" class=xl96> </TD><TD style="BORDER-BOTTOM: gray 0.5pt solid; BORDER-LEFT: gray 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND: #ffffcc; COLOR: windowtext; FONT-SIZE: 10pt; BORDER-TOP: gray 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: gray 0.5pt solid; TEXT-DECORATION: none; mso-pattern: black none; text-underline-style: none; text-line-through: none" class=xl89> </TD><TD style="BORDER-BOTTOM: gray 0.5pt solid; BORDER-LEFT: gray 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND: #ffffcc; COLOR: windowtext; FONT-SIZE: 10pt; BORDER-TOP: gray 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: gray 1pt solid; TEXT-DECORATION: none; mso-pattern: black none; text-underline-style: none; text-line-through: none" class=xl90> </TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: gray 0.5pt solid; BORDER-LEFT: gray 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND: #ffffcc; HEIGHT: 15pt; COLOR: windowtext; FONT-SIZE: 10pt; BORDER-TOP: gray 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: #f0f0f0; TEXT-DECORATION: none; mso-pattern: black none; text-underline-style: none; text-line-through: none" class=xl91 height=20> </TD><TD style="BORDER-BOTTOM: gray 0.5pt solid; BORDER-LEFT: gray 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND: #ffffcc; COLOR: windowtext; FONT-SIZE: 10pt; BORDER-TOP: gray 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: gray 0.5pt solid; TEXT-DECORATION: none; mso-pattern: black none; text-underline-style: none; text-line-through: none" class=xl92> </TD><TD style="BORDER-BOTTOM: gray 0.5pt solid; BORDER-LEFT: gray 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND: #ffffcc; COLOR: windowtext; FONT-SIZE: 10pt; BORDER-TOP: gray 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: gray 0.5pt solid; TEXT-DECORATION: none; mso-pattern: black none; text-underline-style: none; text-line-through: none" class=xl96> </TD><TD style="BORDER-BOTTOM: gray 0.5pt solid; BORDER-LEFT: gray 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND: #ffffcc; COLOR: windowtext; FONT-SIZE: 10pt; BORDER-TOP: gray 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: gray 0.5pt solid; TEXT-DECORATION: none; mso-pattern: black none; text-underline-style: none; text-line-through: none" class=xl89> </TD><TD style="BORDER-BOTTOM: gray 0.5pt solid; BORDER-LEFT: gray 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND: #ffffcc; COLOR: windowtext; FONT-SIZE: 10pt; BORDER-TOP: gray 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: gray 1pt solid; TEXT-DECORATION: none; mso-pattern: black none; text-underline-style: none; text-line-through: none" class=xl90> </TD><TD style="BORDER-BOTTOM: gray 0.5pt solid; BORDER-LEFT: gray 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND: #ffffcc; COLOR: windowtext; FONT-SIZE: 10pt; BORDER-TOP: gray 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: gray 0.5pt solid; TEXT-DECORATION: none; mso-pattern: black none; text-underline-style: none; text-line-through: none" class=xl96> </TD><TD style="BORDER-BOTTOM: gray 0.5pt solid; BORDER-LEFT: gray 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND: #ffffcc; COLOR: windowtext; FONT-SIZE: 10pt; BORDER-TOP: gray 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: gray 0.5pt solid; TEXT-DECORATION: none; mso-pattern: black none; text-underline-style: none; text-line-through: none" class=xl89> </TD><TD style="BORDER-BOTTOM: gray 0.5pt solid; BORDER-LEFT: gray 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND: #ffffcc; COLOR: windowtext; FONT-SIZE: 10pt; BORDER-TOP: gray 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: gray 1pt solid; TEXT-DECORATION: none; mso-pattern: black none; text-underline-style: none; text-line-through: none" class=xl90> </TD><TD style="BORDER-BOTTOM: gray 0.5pt solid; BORDER-LEFT: gray 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND: #ffffcc; COLOR: windowtext; FONT-SIZE: 10pt; BORDER-TOP: gray 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: gray 0.5pt solid; TEXT-DECORATION: none; mso-pattern: black none; text-underline-style: none; text-line-through: none" class=xl96> </TD><TD style="BORDER-BOTTOM: gray 0.5pt solid; BORDER-LEFT: gray 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND: #ffffcc; COLOR: windowtext; FONT-SIZE: 10pt; BORDER-TOP: gray 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: gray 0.5pt solid; TEXT-DECORATION: none; mso-pattern: black none; text-underline-style: none; text-line-through: none" class=xl89> </TD><TD style="BORDER-BOTTOM: gray 0.5pt solid; BORDER-LEFT: gray 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND: #ffffcc; COLOR: windowtext; FONT-SIZE: 10pt; BORDER-TOP: gray 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: gray 1pt solid; TEXT-DECORATION: none; mso-pattern: black none; text-underline-style: none; text-line-through: none" class=xl90> </TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: gray 0.5pt solid; BORDER-LEFT: gray 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: gray; BORDER-RIGHT: #f0f0f0" class=xl91 height=20 align=right>20,000.00</TD><TD style="BORDER-BOTTOM: gray 0.5pt solid; BORDER-LEFT: gray 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: gray; BORDER-RIGHT: gray 0.5pt solid" class=xl92 align=right>0.00</TD><TD style="BORDER-BOTTOM: gray 0.5pt solid; BORDER-LEFT: gray 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND: #ffffcc; COLOR: windowtext; FONT-SIZE: 10pt; BORDER-TOP: gray 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: gray 0.5pt solid; TEXT-DECORATION: none; mso-pattern: black none; text-underline-style: none; text-line-through: none" class=xl96> </TD><TD style="BORDER-BOTTOM: gray 0.5pt solid; BORDER-LEFT: gray; BACKGROUND-COLOR: transparent; BORDER-TOP: gray; BORDER-RIGHT: gray 0.5pt solid" class=xl89 align=right>20,000.00</TD><TD style="BORDER-BOTTOM: gray 0.5pt solid; BORDER-LEFT: gray 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND: #ffffcc; COLOR: windowtext; FONT-SIZE: 10pt; BORDER-TOP: gray 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: gray 1pt solid; TEXT-DECORATION: none; mso-pattern: black none; text-underline-style: none; text-line-through: none" class=xl90> </TD><TD style="BORDER-BOTTOM: gray 0.5pt solid; BORDER-LEFT: gray 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND: #ffffcc; COLOR: windowtext; FONT-SIZE: 10pt; BORDER-TOP: gray 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: gray 0.5pt solid; TEXT-DECORATION: none; mso-pattern: black none; text-underline-style: none; text-line-through: none" class=xl96> </TD><TD style="BORDER-BOTTOM: gray 0.5pt solid; BORDER-LEFT: gray 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND: #ffffcc; COLOR: windowtext; FONT-SIZE: 10pt; BORDER-TOP: gray 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: gray 0.5pt solid; TEXT-DECORATION: none; mso-pattern: black none; text-underline-style: none; text-line-through: none" class=xl89> </TD><TD style="BORDER-BOTTOM: gray 0.5pt solid; BORDER-LEFT: gray 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND: #ffffcc; COLOR: windowtext; FONT-SIZE: 10pt; BORDER-TOP: gray 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: gray 1pt solid; TEXT-DECORATION: none; mso-pattern: black none; text-underline-style: none; text-line-through: none" class=xl90> </TD><TD style="BORDER-BOTTOM: gray 0.5pt solid; BORDER-LEFT: gray 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND: #ffffcc; COLOR: windowtext; FONT-SIZE: 10pt; BORDER-TOP: gray 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: gray 0.5pt solid; TEXT-DECORATION: none; mso-pattern: black none; text-underline-style: none; text-line-through: none" class=xl96> </TD><TD style="BORDER-BOTTOM: gray 0.5pt solid; BORDER-LEFT: gray 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND: #ffffcc; COLOR: windowtext; FONT-SIZE: 10pt; BORDER-TOP: gray 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: gray 0.5pt solid; TEXT-DECORATION: none; mso-pattern: black none; text-underline-style: none; text-line-through: none" class=xl89> </TD><TD style="BORDER-BOTTOM: gray 0.5pt solid; BORDER-LEFT: gray 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND: #ffffcc; COLOR: windowtext; FONT-SIZE: 10pt; BORDER-TOP: gray 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: gray 1pt solid; TEXT-DECORATION: none; mso-pattern: black none; text-underline-style: none; text-line-through: none" class=xl90> </TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: gray 0.5pt solid; BORDER-LEFT: gray 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND: #ffffcc; HEIGHT: 15pt; COLOR: windowtext; FONT-SIZE: 10pt; BORDER-TOP: gray 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: #f0f0f0; TEXT-DECORATION: none; mso-pattern: black none; text-underline-style: none; text-line-through: none" class=xl91 height=20> </TD><TD style="BORDER-BOTTOM: gray 0.5pt solid; BORDER-LEFT: gray 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND: #ffffcc; COLOR: windowtext; FONT-SIZE: 10pt; BORDER-TOP: gray 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: gray 0.5pt solid; TEXT-DECORATION: none; mso-pattern: black none; text-underline-style: none; text-line-through: none" class=xl92> </TD><TD style="BORDER-BOTTOM: gray 0.5pt solid; BORDER-LEFT: gray 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND: #ffffcc; COLOR: windowtext; FONT-SIZE: 10pt; BORDER-TOP: gray 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: gray 0.5pt solid; TEXT-DECORATION: none; mso-pattern: black none; text-underline-style: none; text-line-through: none" class=xl96> </TD><TD style="BORDER-BOTTOM: gray 0.5pt solid; BORDER-LEFT: gray 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND: #ffffcc; COLOR: windowtext; FONT-SIZE: 10pt; BORDER-TOP: gray 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: gray 0.5pt solid; TEXT-DECORATION: none; mso-pattern: black none; text-underline-style: none; text-line-through: none" class=xl89> </TD><TD style="BORDER-BOTTOM: gray 0.5pt solid; BORDER-LEFT: gray 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND: #ffffcc; COLOR: windowtext; FONT-SIZE: 10pt; BORDER-TOP: gray 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: gray 1pt solid; TEXT-DECORATION: none; mso-pattern: black none; text-underline-style: none; text-line-through: none" class=xl90> </TD><TD style="BORDER-BOTTOM: gray 0.5pt solid; BORDER-LEFT: gray 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND: #ffffcc; COLOR: windowtext; FONT-SIZE: 10pt; BORDER-TOP: gray 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: gray 0.5pt solid; TEXT-DECORATION: none; mso-pattern: black none; text-underline-style: none; text-line-through: none" class=xl96> </TD><TD style="BORDER-BOTTOM: gray 0.5pt solid; BORDER-LEFT: gray 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND: #ffffcc; COLOR: windowtext; FONT-SIZE: 10pt; BORDER-TOP: gray 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: gray 0.5pt solid; TEXT-DECORATION: none; mso-pattern: black none; text-underline-style: none; text-line-through: none" class=xl89> </TD><TD style="BORDER-BOTTOM: gray 0.5pt solid; BORDER-LEFT: gray 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND: #ffffcc; COLOR: windowtext; FONT-SIZE: 10pt; BORDER-TOP: gray 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: gray 1pt solid; TEXT-DECORATION: none; mso-pattern: black none; text-underline-style: none; text-line-through: none" class=xl90> </TD><TD style="BORDER-BOTTOM: gray 0.5pt solid; BORDER-LEFT: gray 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND: #ffffcc; COLOR: windowtext; FONT-SIZE: 10pt; BORDER-TOP: gray 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: gray 0.5pt solid; TEXT-DECORATION: none; mso-pattern: black none; text-underline-style: none; text-line-through: none" class=xl96> </TD><TD style="BORDER-BOTTOM: gray 0.5pt solid; BORDER-LEFT: gray 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND: #ffffcc; COLOR: windowtext; FONT-SIZE: 10pt; BORDER-TOP: gray 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: gray 0.5pt solid; TEXT-DECORATION: none; mso-pattern: black none; text-underline-style: none; text-line-through: none" class=xl89> </TD><TD style="BORDER-BOTTOM: gray 0.5pt solid; BORDER-LEFT: gray 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND: #ffffcc; COLOR: windowtext; FONT-SIZE: 10pt; BORDER-TOP: gray 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: gray 1pt solid; TEXT-DECORATION: none; mso-pattern: black none; text-underline-style: none; text-line-through: none" class=xl90> </TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: gray 0.5pt solid; BORDER-LEFT: gray 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND: #ffffcc; HEIGHT: 15pt; COLOR: windowtext; FONT-SIZE: 10pt; BORDER-TOP: gray 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: #f0f0f0; TEXT-DECORATION: none; mso-pattern: black none; text-underline-style: none; text-line-through: none" class=xl91 height=20> </TD><TD style="BORDER-BOTTOM: gray 0.5pt solid; BORDER-LEFT: gray 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND: #ffffcc; COLOR: windowtext; FONT-SIZE: 10pt; BORDER-TOP: gray 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: gray 0.5pt solid; TEXT-DECORATION: none; mso-pattern: black none; text-underline-style: none; text-line-through: none" class=xl92> </TD><TD style="BORDER-BOTTOM: gray 0.5pt solid; BORDER-LEFT: gray 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND: #ffffcc; COLOR: windowtext; FONT-SIZE: 10pt; BORDER-TOP: gray 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: gray 0.5pt solid; TEXT-DECORATION: none; mso-pattern: black none; text-underline-style: none; text-line-through: none" class=xl96> </TD><TD style="BORDER-BOTTOM: gray 0.5pt solid; BORDER-LEFT: gray 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND: #ffffcc; COLOR: windowtext; FONT-SIZE: 10pt; BORDER-TOP: gray 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: gray 0.5pt solid; TEXT-DECORATION: none; mso-pattern: black none; text-underline-style: none; text-line-through: none" class=xl89> </TD><TD style="BORDER-BOTTOM: gray 0.5pt solid; BORDER-LEFT: gray 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND: #ffffcc; COLOR: windowtext; FONT-SIZE: 10pt; BORDER-TOP: gray 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: gray 1pt solid; TEXT-DECORATION: none; mso-pattern: black none; text-underline-style: none; text-line-through: none" class=xl90> </TD><TD style="BORDER-BOTTOM: gray 0.5pt solid; BORDER-LEFT: gray 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND: #ffffcc; COLOR: windowtext; FONT-SIZE: 10pt; BORDER-TOP: gray 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: gray 0.5pt solid; TEXT-DECORATION: none; mso-pattern: black none; text-underline-style: none; text-line-through: none" class=xl96> </TD><TD style="BORDER-BOTTOM: gray 0.5pt solid; BORDER-LEFT: gray 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND: #ffffcc; COLOR: windowtext; FONT-SIZE: 10pt; BORDER-TOP: gray 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: gray 0.5pt solid; TEXT-DECORATION: none; mso-pattern: black none; text-underline-style: none; text-line-through: none" class=xl89> </TD><TD style="BORDER-BOTTOM: gray 0.5pt solid; BORDER-LEFT: gray 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND: #ffffcc; COLOR: windowtext; FONT-SIZE: 10pt; BORDER-TOP: gray 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: gray 1pt solid; TEXT-DECORATION: none; mso-pattern: black none; text-underline-style: none; text-line-through: none" class=xl90> </TD><TD style="BORDER-BOTTOM: gray 0.5pt solid; BORDER-LEFT: gray 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND: #ffffcc; COLOR: windowtext; FONT-SIZE: 10pt; BORDER-TOP: gray 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: gray 0.5pt solid; TEXT-DECORATION: none; mso-pattern: black none; text-underline-style: none; text-line-through: none" class=xl96> </TD><TD style="BORDER-BOTTOM: gray 0.5pt solid; BORDER-LEFT: gray 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND: #ffffcc; COLOR: windowtext; FONT-SIZE: 10pt; BORDER-TOP: gray 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: gray 0.5pt solid; TEXT-DECORATION: none; mso-pattern: black none; text-underline-style: none; text-line-through: none" class=xl89> </TD><TD style="BORDER-BOTTOM: gray 0.5pt solid; BORDER-LEFT: gray 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND: #ffffcc; COLOR: windowtext; FONT-SIZE: 10pt; BORDER-TOP: gray 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: gray 1pt solid; TEXT-DECORATION: none; mso-pattern: black none; text-underline-style: none; text-line-through: none" class=xl90> </TD></TR></TBODY></TABLE>

Hi, above is an example of excerpt of my spreadsheet. I am using the following formula,
=IF(C7="","",SUM(IF(O7<>"nsf",P7,0),IF(T7<>"nsf",U7,0),IF(Y7<>"nsf",Z7,0),IF(AD7<>"nsf",AE7,0),IF(AI7<>"nsf",AJ7,0),IF(AN7<>"nsf",AO7,0),IF(AS7<>"nsf",AT7,0),IF(AX7<>"nsf",AY7,0),IF(BC7<>"nsf",BD7,0),IF(BH7<>"nsf",BI7,0),IF(BM7<>"nsf",BN7,0),IF(BR7<>"nsf",BS7,0)))
which works, however as more cheques gets added, I will run out of space.
Am wondering if there is an array formula than can be used. I tried various ways but can't get the result. Will you help?
 

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.
Sort by date Sort by votes
The example of excerpt doesn't really paint the picture of your data.... Where is C7? Which cells are examples of O7 and P7?
 
Upvote 0
This uses the column Headers in row 1 to determine if the column to sum is an "Amount" column and if the cell to the left of the amount columns is <> "nsf"

Code: 
=IF(C7="","",SUMPRODUCT($P7:$HB7, --($P$1:$HB$1="Amount"), --($O7:$HA7<>"nsf")))

This sums the cells in row 7; $P7:$HB7
When the cells in row 1 = "Amount"; --($P$1:$HB$1="Amount")
And the adjacent cells to the left are <> "nsf"; --($O7:$HA7<>"nsf")
Note that the "Amount" range $P$1:$HB$1 is offset by 1 column from the "nsf" range $O7:$HA7. This is important.

If you have Excel 2003 or earlier, use the above SUMPRODUCT formula. If you have Excel 2007 or later, you could use the SUMIFS formula below.

Code: 
=IF(C7="","",SUMIFS($P7:$HB7, $P$1:$HB$1,"Amount", $O7:$HA7, "<>nsf"))
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Solved
VBA Function to Paste Table into Outlook Email Resulting in HTML language in Email
Replies
8
Views
616
smozgur
smozgur
E
  • Question Question
VBA code clean up
Replies
9
Views
573
mumps
mumps
G
  • Question Question
Using Excel formula to create HTML code text referencing specific cells
Replies
0
Views
1K
gvsumich
G
A
  • Question Question
VBA - Automatically Break the Line in HTML format.
Replies
7
Views
2K
Attrazion
A
C
  • Solved
Excel 2016 32bit VBA Pulling info from Website to Sheet
Replies
2
Views
2K
CrashOD
C

Forum statistics

Threads
1,224,507
Messages
6,179,183
Members
452,893
Latest member
denay

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top