Sumifs Match Formula.

[srizki]

I need assistance creating a formula for column G in my Excel sheet. The formula should be able to match either column B or C, then search in another sheet named "PGL" for the cost center in column F or text in column E, and retrieve the value from column "CC".
If there are values in column C, the corresponding cell in column B is blank and vice versa.
Also, if there are more than one matches, I want the sum of the matching values.
Thank you very much.

 

[Cubist]

The formula should be able to match either column B or C,

Match what to B or C?

then search in another sheet named "PGL" for the cost center in column F or text in column E

search what?

Can you post a mockup example?

 

[srizki]

Match what to B or C?


search what?

Can you post a mockup example?

it is staraight forward, if the lookup value is in column B, use that lookup value and match that value with column F of sheet PGL. If there is nothing in column C there there must be text in column B, use that as a lookup value and match with values in column E of sheet PGL, and in both of these lookup values I need the sum of values from column CC in PGL,.

 

[dreid1011]

it is staraight forward, if the lookup value is in column B, use that lookup value and match that value with column F of sheet PGL. If there is nothing in column C there there must be text in column B, use that as a lookup value and match with values in column E of sheet PGL, and in both of these lookup values I need the sum of values from column CC in PGL,.

It may be straight forward to you, but without a visual of your data with the expected results, and some vague-ish description provided, it is not clear or straight forward to us. You didn't say which lookup value (B or C) should match with columns F or E. So, if you would please, provide a sample of your data and the expected results.

 

[srizki]

It may be straight forward to you, but without a visual of your data with the expected results, and some vague-ish description provided, it is not clear or straight forward to us. You didn't say which lookup value (B or C) should match with columns F or E. So, if you would please, provide a sample of your data and the expected results.If

It may be straight forward to you, but without a visual of your data with the expected results, and some vague-ish description provided, it is not clear or straight forward to us. You didn't say which lookup value (B or C) should match with columns F or E. So, if you would please, provide a sample of your data and the expected results.

It may be straight forward to you, but without a visual of your data with the expected results, and some vague-ish description provided, it is not clear or straight forward to us. You didn't say which lookup value (B or C) should match with columns F or E. So, if you would please, provide a sample of your data and the expected results.

If there is value in sheet 1, column C, then the matching value range on sheet 2 will be in column F, if that cell on sheet column C is blank,, then there must a value in column B, so we the lookup value is in column B that should match the range in sheet 2 column E. and the sum value is in column CC. 1

let me start over.
Sheet1, lookup values in columns B and C, if there are values in column B, the corresponding cell in column C is empty. vise versa, the range is on sheet 2, for column C in sheet1 the lookup up range is in column F on sheet2, and the lookup value on sheet1 column B corresponding range is in E sheet 2, in both of these ranges the Sum value could be S on Sheet2, lets say the sum range is in column CC of sheet2.
So basically the sume if formula will be =SUMIF(sheet2!FF,sheet1!C15,sheet2!CC), but I need two formulas one for lookup value in column C and another one for the lookup value in Column B. but the sum range is same for the both of these lookup up values. CC on sheet 2.

 

[dreid1011]

in both of these ranges the Sum value could be S on Sheet2

Not sure how this applies, but it would still be incredibly helpful if you provided a sample of your data from both sheets and expected results. Some of us work better when we can see the problem.

But maybe this to start:

Book1
	B	C	D	E
2	One			2300
3		Two		2900
4	Three			1200
5		Four		1300
Sheet1

Cell Formulas
Range	Formula
E2:E5	E2	=SUMIF(Sheet2!$E$2:$E$17,B2,Sheet2!$CC$2:$CC$17)+SUMIF(Sheet2!$F$2:$F$17,C2,Sheet2!$CC$2:$CC$17)

Book1
	E	F																																																																											CC
2	One																																																																												500
3		Two																																																																											1000
4	Three																																																																												300
5		Four																																																																											400
6	One																																																																												700
7		Two																																																																											800
8	Three																																																																												500
9		Four																																																																											300
10	One																																																																												200
11		Two																																																																											100
12	Three																																																																												300
13		Four																																																																											400
14	One																																																																												900
15		Two																																																																											1000
16	Three																																																																												100
17		Four																																																																											200
Sheet2