SUMIF double counting issue

[Tjadi]

Hi all,

I am having a problem with a double counting sumif, because of and array.*It has 3 columns, A: expertises. B: OB (not relevant for this issue) and C: the amount of events. I want to count the total amount of events for some expertises. A cell in column A might contain multiple expertises (for example: Banking / Finance, PR / Communicatie, HRM / Recruitment, IT / ICT, Management, Marketing / Product Management, Sales).

I want to sum up all the events 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 some values. This is as a cell might contain two of the possibilities. Is it possible to only count a row once?

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

[Bernie Deitrick]

You can use an array-function, entered with Ctrl-Shift-Enter. I use , instead of ; so if I messed up on the replacement I had no way of checking the result:

=SUM(IF(ISNUMBER(SEARCH("Banking";A2:A4));1;IF(ISNUMBER(SEARCH("Juridisch";A2:A4));1;IF(ISNUMBER(SEARCH(Communicatie;A2:A4));1;0)))*C2:C4)

[Phuocam]

Or try:

=SUMPRODUCT((COUNT(SEARCH({"Banking","Juridisch","Communicatie"},A2:A4))>0)*C2:C4)