If a cell begins with OP

[Mia_march]

I have got a spreadsheet where a need to sum some income up, but only when they are in particular areas.
For example D6 can be OPTEACHING, OPADMIN or OPMANAGE and the income will be in V6, I need to sum this if D6 begins with OP. I don't know whether it would be easier to have another column where I identify the OP or otherwise and then do the income if this column is OP

 

[VoG]

Try like this

=SUMIF(D6:D100,"OP*",V6:V100)

 

[Weaver]

Welcome to the board.

What you need is =SUMIF

I'm guessing your headers are in row 5, but I don't know where your data ends. For this example, let's assume it's row 1000

=SUMIF(D6:D1000,"OP*",V6:V1000)

You should be able to adjust this to suit.

HTH

 

[Jonmo1]

Welcome to the board...

You can use a wildcard in Sumif like this

=SUMIF(A1:A0,"OP*",B1:B0)


Hope that helps.

 

[Mia_march]

This is great, thanks - unfortunately I've just realised I haven't put all the arguments that I need in the query.

I have got a table that I am putting all this information into and I also need to know put the total in if G6 is less than 01/08/11 or 01/08/11 or more and also whether L6 is 16-18.

Thank you

 

[Weaver]

Thought that was a bit too simple!

For multiple arguments, you're going to have to use =SUMPRODUCT

=SUMPRODUCT(--(LEFT(A6:A1000,2)="OP")*(G6:G1000<DATEVALUE("01/08/11")*(L6:L1000>=16)*(L6:L1000<=18),V6:V1000)

I think

 

[Mia_march]

This doesn't quite solve the problem, I will have 4 tables in a separate sheet, one which counts the finance for 16-18 (which will have 16-18 in the cell) year olds and 19+ for starters (which will have either 19-24, 25-49 or 50+ in the cell) before 01/08/11 and another that counts the finance for 16-18 year olds and 19+ for starters after 01/08/11. See below if that helps.

<TABLE style="WIDTH: 269pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=359><COLGROUP><COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 69pt; mso-width-source: userset; mso-width-alt: 3364" width=92><COL style="WIDTH: 53pt; mso-width-source: userset; mso-width-alt: 2596" width=71><COL style="WIDTH: 51pt; mso-width-source: userset; mso-width-alt: 2486" width=68><COL style="WIDTH: 48pt" width=64><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 48pt; HEIGHT: 12.75pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" id=td_post_2913678 class=xl66 height=17 width=64> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 69pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 width=92>D</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 53pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 width=71>G</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 51pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 width=68>L</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 width=64>V</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 height=17> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>Cohort</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>Prog start</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>Age band</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>Amount</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 height=17 align=right>1</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66>OPTEACHING</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 align=right>01/08/2011</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66>25-49</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68 align=right>19.87</TD></TR></TBODY></TABLE>

 

[Weaver]

=SUMPRODUCT(--(LEFT(A6:A1000,2)="OP")*(G6:G1000=datevalue("01/08/11"))*(L6:L1000="16-18"),V6:V1000)

On the other hand, maybe you should have another column with

=left(A6,2)

in it, then use a pivot table, with either the date or the age band in the page field.