Double count issue. SUMIFS/SUMIF

[Tjadi]

Hi all,

I am having a problem with a double counting sumif, because of and array.

<style type="text/css"><!--td {border: 1px solid #ccc;}br {mso-data-placement:same-cell;}--></style>[TABLE="width: 0"]
<colgroup><col style="width: 232px"><col width="100"><col width="100"></colgroup><tbody>[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=f4f4f4]#f4f4f4[/URL] "]Expertises[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=f4f4f4]#f4f4f4[/URL] "]OB[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=f4f4f4]#f4f4f4[/URL] , align: right"]Total Events[/TD]
[/TR]
[TR]
[TD]Banking / Finance, PR / Communicatie, HRM / Recruitment, IT / ICT, Management, Marketing / Product Management, Sales[/TD]
[TD]OB[/TD]
[TD="align: right"]222[/TD]
[/TR]
[TR]
[TD]Juridisch[/TD]
[TD]OB[/TD]
[TD="align: right"]205[/TD]
[/TR]
[TR]
[TD]IT / ICT, Marketing / Product Management, Sales[/TD]
[TD]OB[/TD]
[TD="align: right"]187[/TD]
[/TR]
</tbody>[/TABLE]

I want to count up all the numbers of the expertises Banking / Juridisch /Communicatie. I created the following formula:

=ArrayFormula(SUM(SUM.If(A2:A4;{"*Banking*";"*Communicatie*";"*Juridisch*"};C2:C4)))

But it double counts the first row. Is it possible to only count a row once? The outcome in this case was 649, instead of the intended 427.

Hope someone knows a way of doing this! Looking forward to hearing any solutions.

 

[steve the fish]

These work in excel:

=SUM(IF(MMULT(0+ISNUMBER(SEARCH({"*Banking*","*Communicatie*","*Juridisch*"},A2:A4)),{1;1;1})>0,C2:C4))
=SUMPRODUCT(C2:C4,--(ISNUMBER(SEARCH("Banking",A2:A4))+ISNUMBER(SEARCH("Communicatie",A2:A4))+ISNUMBER(SEARCH("Juridisch",A2:A4))>0))

 

[Tjadi]

Thank you so much! I spend ages on it and couldn't work it out. Second one is perfect for what I am doing.