hlookup & sumifs

[vikishah]

Hello,
My file has company codes(CoCd) in col's and Profit centers(PC) in rows, with $'s assigned to them. I need to retrieve $'s specific to a PC and CoCd combination. Issue is sometimes same CoCds are in multiple columns. So can't do a simple sumif or Hlookup.

E.g.
B30 PC0001
B31 PC0002
B32 PC0003

C29 CoCd1
D29 CoCd2
E29 CoCd1
F20 CoCd3

I need to get the $'s that are assigned to a combination of PC/CoCd.
Please advise. Thank you.

 

[Eric W]

Try:

	A	B	C	D	E	F
PC	PC0002
CoCd	CoCd1
Total
CoCd1	CoCd2	CoCd1	CoCd3
PC0001
PC0002
PC0003

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]26[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]27[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]28[/TD]

[TD="align: right"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]29[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]30[/TD]
[TD="align: right"][/TD]

[TD="align: right"]1[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]10[/TD]

[TD="align: center"]31[/TD]
[TD="align: right"][/TD]

[TD="align: right"]2[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]
8[/TD]
[TD="align: right"]11[/TD]

[TD="align: center"]32[/TD]
[TD="align: right"][/TD]

[TD="align: right"]3[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]12[/TD]

</tbody>

Sheet3​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: rgb
(218,231,245)"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B28[/TH]
[TD="align: left"]=SUMPRODUCT(C30:F32*(B30:B32=B26)*(C29:F29=B27))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[vikishah]

Thank you Eric. But I am getting an N/A using SUMPRODUCT(C30:F32*(B30:B32=B26)*(C29:F29=B27)).
Am I missing something?

Try:

	A	B	C	D	E	F
PC	PC0002
CoCd	CoCd1
Total
CoCd1	CoCd2	CoCd1	CoCd3
PC0001
PC0002
PC0003

<tbody>
[TD="align: center"]26[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]27[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]28[/TD]

[TD="align: right"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]29[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]30[/TD]
[TD="align: right"][/TD]

[TD="align: right"]1[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]10[/TD]

[TD="align: center"]31[/TD]
[TD="align: right"][/TD]

[TD="align: right"]2[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]11[/TD]

[TD="align: center"]32[/TD]
[TD="align: right"][/TD]

[TD="align: right"]3[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]12[/TD]

</tbody>

Sheet3​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH]B28[/TH]
[TD="align: left"]=SUMPRODUCT(C30:F32*(B30:B32=B26)*(C29:F29=B27))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[Eric W]

I'm not sure what to tell you. I get a 0 if one of the values in B26:B27 don't match, and a #VALUE if I put non-numeric data in the table somewhere. Blank entries are OK. I can't create a #N/A. Make sure that your ranges are correct, and that there's valid data in your ranges. Make sure that the formula is written properly. What version of Excel are you using?