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!

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Item Group[/TD]
[TD]Item Code[/TD]
[TD]Total Amt (Using SUMIFS)[/TD]
[TD]Manual Calculation[/TD]
[/TR]
[TR]
[TD]52-21-01[/TD]
[TD]C1, A1, A2, A4, A12[/TD]
[TD]NA [/TD]
[TD]$687.00[/TD]
[/TR]
[TR]
[TD]52-21-01[/TD]
[TD]C2[/TD]
[TD]$66.00[/TD]
[TD]$66.00[/TD]
[/TR]
[TR]
[TD]52-21-01[/TD]
[TD]C1, A1, A2, A4, A12, A14[/TD]
[TD]NA[/TD]
[TD]$984.00[/TD]
[/TR]
[TR]
[TD]52-21-01[/TD]
[TD]C2[/TD]
[TD]$66.00[/TD]
[TD]$66.00[/TD]
[/TR]
[TR]
[TD]52-09-01[/TD]
[TD]C1[/TD]
[TD]$652.00[/TD]
[TD]$652.00[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Item Price List[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Item Group[/TD]
[TD]Item Code[/TD]
[TD]Price[/TD]
[/TR]
[TR]
[TD]52-09-01[/TD]
[TD]C1[/TD]
[TD]$652.00[/TD]
[/TR]
[TR]
[TD]52-09-01[/TD]
[TD]P2[/TD]
[TD]$28,556.00[/TD]
[/TR]
[TR]
[TD]52-21-01[/TD]
[TD]C1[/TD]
[TD]$92.00[/TD]
[/TR]
[TR]
[TD]52-21-01[/TD]
[TD]C2[/TD]
[TD]$66.00[/TD]
[/TR]
[TR]
[TD]52-21-01[/TD]
[TD]C1, A1, A2, A4, A12, A17[/TD]
[TD]$687.00[/TD]
[/TR]
[TR]
[TD]52-21-01[/TD]
[TD]A14[/TD]
[TD]$297.00[/TD]
[/TR]
</tbody>[/TABLE]

 

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

[TABLE="class: cms_table_grid, width: 500"]
<tbody>[TR]
[TD]52-21-01[/TD]
[TD]C1, A1, A2, A4, A12, A17[/TD]
[TD]$687.00[/TD]
[/TR]
[TR]
[TD]52-21-01[/TD]
[TD]A14[/TD]
[TD]$297.00[/TD]
[/TR]
</tbody>[/TABLE]

And from the input area ...

[TABLE="class: cms_table_grid, width: 500"]
<tbody>[TR]
[TD]52-21-01[/TD]
[TD]C1, A1, A2, A4, A12, A14[/TD]
[/TR]
</tbody>[/TABLE]


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