Sumifs

[thatholycow]

Hi,

I am unable to derive below 2 amounts using SUMIFS.

Item code (e.g. C1, A1, A2, A4, A12) cannot be split due they are a cluster.

Appreciate if someone is able to enlighten me.

Many thanks in advance!

Item Group	Item Code	Total Amt (Using SUMIFS)	Manual Calculation
52-21-01	C1, A1, A2, A4, A12	NA	$687.00
52-21-01	C2	$66.00	$66.00
52-21-01	C1, A1, A2, A4, A12, A14	NA	$984.00
52-21-01	C2	$66.00	$66.00
52-09-01	C1	$652.00	$652.00

<tbody>
</tbody>

Item Price List
Item Group	Item Code	Price
52-09-01	C1	$652.00
52-09-01	P2	$28,556.00
52-21-01	C1	$92.00
52-21-01	C2	$66.00
52-21-01	C1, A1, A2, A4, A12, A17	$687.00
52-21-01	A14	$297.00

<tbody>
</tbody>

 

[James006]

Re: Need Help in SUMIFS

Hello,

How do you come up with your manual calculations in cells D2 and D4 ...

Is A17 missing in cell B2 ...?

 

[thatholycow]

Re: Need Help in SUMIFS

Hi,

Please see below my reply.

How do you come up with your manual calculations in cells D2 and D4 ...
- Refer to Item Price List table, and look at 52-21-01, C2. You will get $66.00

Is A17 missing in cell B2 ...?
- Nope, B3 is part of Item Price List table B7.

 

[James006]

Re: Need Help in SUMIFS

Hi,

Understand your lookup objective ...

How do you come up with $984.00 ...???

 

[thatholycow]

Re: Need Help in SUMIFS

Hello,

$687+$297 = $984

 

[James006]

Re: Need Help in SUMIFS

Hello,

From your Table :

52-21-01	C1, A1, A2, A4, A12, A17	$687.00
52-21-01	A14	$297.00

<tbody>
</tbody>

And from the input area ...

52-21-01

C1, A1, A2, A4, A12, A14

<tbody>
</tbody>

Is their a rule to separate out the last element A14 ...??? AND not to consider the last element in Table A17 ...???