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
26	PC	PC0002
27	CoCd	CoCd1
28	Total	10
29			CoCd1	CoCd2	CoCd1	CoCd3
30		PC0001	1	4	7	10
31		PC0002	2	5	8	11
32		PC0003	3	6	9	12

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet3​

Worksheet Formulas Cell Formula B28 =SUMPRODUCT(C30:F32*(B30:B32=B26)*(C29:F29=B27)) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

 

[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
26	PC	PC0002
27	CoCd	CoCd1
28	Total	10
29			CoCd1	CoCd2	CoCd1	CoCd3
30		PC0001	1	4	7	10
31		PC0002	2	5	8	11
32		PC0003	3	6	9	12

<tbody>
</tbody>

Sheet3​

Worksheet Formulas Cell Formula B28 =SUMPRODUCT(C30:F32*(B30:B32=B26)*(C29:F29=B27)) <tbody> </tbody>

<tbody>
</tbody>

 

[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?