We help IT Professionals succeed at work.

# combining values in a formula

on
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"
Comment
Watch Question

## View Solution Only

Partner

Commented:
Report!T:T,"Q1-2015"

means nothing !!!
can you post your formula ?
gowflow
Enterprise Architect

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
Partner

Commented:
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
Enterprise Architect

Commented:
formula did not work
Partner

Commented:
ok can you post the workbook ?
gowflow
Enterprise Architect

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
Partner

Commented:
Sorry don't understand your last post !
gowflow
Enterprise Architect

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
Partner

Commented:
Again can't you post your data ? much easier to understand.
gowflow
Enterprise Architect

Commented:
Data is sensitive would take me about an hour to cleanup
Partner

Commented:
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
Enterprise Architect

Commented:
yes exactly
Partner

Commented:
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
Partner

Commented:
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
Finance Analyst
Commented:
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