combining values in a formula

I have the following formula that works great but I need it to check for q1-2015 or q2-2015 can I do that in the same check


Report!T:T,"Q1-2015"
Matt PinkstonAsked:
Who is Participating?
 
Rob HensonFinance AnalystCommented:
Back to your original formula, to get two criteria for the same column in a SUMIFS formula, you need to use two SUMIFS. Each of the criteria within a SUMIFS is compared using an AND comparator, ie Criteria1 AND Criteria2. So for your 2015Q1 and 2015Q2, the value in column T can't be both so Q1 AND Q2. So you will need:

=SUMIFS(Report!G:G,Report!L:L,"Civilian",Report!N:N,"0*",Report!N:N,"<>*06*",Report!D:D,"<>*Commit*",Report!U:U,"<>*Forecast*",Report!T:T,"Q1-2015")+=SUMIFS(Report!G:G,Report!L:L,"Civilian",Report!N:N,"0*",Report!N:N,"<>*06*",Report!D:D,"<>*Commit*",Report!U:U,"<>*Forecast*",Report!T:T,"Q2-2015")

Thanks
Rob H
0
 
gowflowCommented:
Report!T:T,"Q1-2015"

means nothing !!!
can you post your formula ?
gowflow
0
 
Matt PinkstonAuthor Commented:
=SUMIFS(Report!G:G,Report!L:L,"Civilian",Report!N:N,"0*",Report!N:N,"<>*06*",Report!D:D,"<>*Commit*",Report!U:U,"<>*Forecast*",Report!T:T,"Q1-2015")


I have to change this in a ton of places "the last part that is" so was hoping for an or type thing
0
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
gowflowCommented:
well we cannot de-mistify things by bits. I can make up a VBA for you that will change the whole thing automatically but need you to post the workbook so I can study its extent.

But given what you have asked you could have something like this try if it works fine then will worry about changing all the formulas

=SUMIFS(Report!G:G,Report!L:L,"Civilian",Report!N:N,"0*",Report!N:N,"<>*06*",Report!D:D,"<>*Commit*",Report!U:U,"<>*Forecast*",Report!T:T,OR("Q1-2015","Q2-2015"))

gowflow
0
 
Matt PinkstonAuthor Commented:
formula did not work
0
 
gowflowCommented:
ok can you post the workbook ?
gowflow
0
 
Matt PinkstonAuthor Commented:
if column T = q1-2015, q2-2015, q3-2015 or q4-2015 and I get a quick formula I can place in another column to set the column to 20151H if T = q1-2015 or q2-2015 and 20152H if T = q3-2015 or q4-2015
0
 
gowflowCommented:
Sorry don't understand your last post !
gowflow
0
 
Matt PinkstonAuthor Commented:
in my data tab...

I need a formula for column W which is currently blank that will set that column to:

20151H if column T = q1-2015 or q2-2015
20152H if column T = q3-2015 or q4-2015
0
 
gowflowCommented:
Again can't you post your data ? much easier to understand.
gowflow
0
 
Matt PinkstonAuthor Commented:
Data is sensitive would take me about an hour to cleanup
0
 
gowflowCommented:
ok so we need only to focus on the last post ?

I need a formula for column W which is currently blank that will set that column to:

 20151H if column T = q1-2015 or q2-2015
 20152H if column T = q3-2015 or q4-2015

or this is to be incorporated in the formula you first posted ?

and when you say I need a formula in W to
20151H if column T = q1-2015 or q2-2015

what do you mean by that ?
look at Col T if value is Q1-2015 or Q2-2015 then make the corresponding value in W 20151H ?

gowflow
0
 
Matt PinkstonAuthor Commented:
yes exactly
0
 
gowflowCommented:
Yes exactly what ? there are 2 questions in the post:

1) Is it to incorporate this W 20151H  in the big formula ?
or
2) Simply create the new formula in Col W ?

gowflow
0
 
gowflowCommented:
If you need option 2) in my previous post then put this formula in W1 and drag it down till end of data.

=IF(OR(LOWER(T1)="q1-2015",LOWER(T1)="q2-2015"),"20151H",IF(OR(LOWER(T1)="q3-2015",LOWER(T1)="q4-2015"),"20152H",""))

gowflow
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.