SUMIF Condition first three characters and array

[MCActive]

I've come quite far with our new excel sheet, mainly because of the tips on this forum; but now we are a little stuck..

I've made a little example of our spreadsheet (only the important fields):

[TABLE="width: 130"]
<tbody>[TR]
[TD="width: 65"]Posting[/TD]
[TD="width: 65"]Total[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]B003[/TD]
[TD="bgcolor: #DCE6F1, align: right"]20[/TD]
[/TR]
[TR]
[TD]B004[/TD]
[TD="align: right"]10[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]B005[/TD]
[TD="bgcolor: #DCE6F1, align: right"]20[/TD]
[/TR]
[TR]
[TD]B100[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]B101[/TD]
[TD="bgcolor: #DCE6F1, align: right"]20[/TD]
[/TR]
[TR]
[TD]C100[/TD]
[TD="align: right"]16[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]C101[/TD]
[TD="bgcolor: #DCE6F1, align: right"]21[/TD]
[/TR]
[TR]
[TD]C102[/TD]
[TD="align: right"]32[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]D001[/TD]
[TD="bgcolor: #DCE6F1, align: right"]43[/TD]
[/TR]
[TR]
[TD]D002[/TD]
[TD="align: right"]23[/TD]
[/TR]
</tbody>[/TABLE]


What we're trying to do; is to make a grand total of the sum of all total fields starting with C10 AND D00, it should say 135.
With the whole number it is no problem at all, with this formula we managed to do so, but we want it to be more flexible..

=SUM(SUM(Table1[Posting];{"C100";"C101";"C102";"D001";"D002"};Table1[Total]))

Most ideal situation would be something like this:
=SUM(SUM(Table1[Posting];{"C10";"D00"};Table1[Total]))

Can anyone help us?

 

[redmosquito98]

Hey there,
Assuming that these are columns A and B then you can use an array formula to do it.
{=sum((B2:B100)*(if(left(a2:a100,3)="C10",1,0)+if(left(a2:a100,3)="D00",1,0)))}
the way to get the "{}" braces in if you haven't done it before is not to type them in but hit ctrl+shift+enter when done typing the formula (cmd+enter on a mac).

 

[MCActive]

Thanks, works very good!