Function Sumif in Excel

Question

I've got a problem with function SUMIF in Excel.

I want to use this function to calculate the total Q of codes that contain "a" and "b" (ex: ababba, abcab, bac) as in the image.

The code I used is SUMIF(K6:K9;"*a*b*";L6:L9)+SUMIF(K6:K9;"*b*a*";L6:L9). However, codes like ababba, abcab are calculated twice.

Can you help me with this? Thank a lot.

Answer 1

Use a simple SUMIFS:

=SUMIFS(L6:L9;K6:K9;"*a*";K6:K9;"*b*")

This will only calculate those that have both.