If a cell begins with OP

Mia_march

New Member
Joined
Oct 31, 2011
Messages
3
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
 

Some videos you may like

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

Weaver

Well-known Member
Joined
Sep 10, 2008
Messages
5,196
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

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
Welcome to the board...

You can use a wildcard in Sumif like this

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


Hope that helps.
 

Mia_march

New Member
Joined
Oct 31, 2011
Messages
3

ADVERTISEMENT

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

Well-known Member
Joined
Sep 10, 2008
Messages
5,196
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

New Member
Joined
Oct 31, 2011
Messages
3
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

Well-known Member
Joined
Sep 10, 2008
Messages
5,196
=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.
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,123,489
Messages
5,601,986
Members
414,489
Latest member
Xlambda

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
Top