Advanced SumIf criteria

[pkaff]

Hello!

I've come up with a solution for summing the values of column B if the corresponding value in column A contains a substring that matches any of the values in a given list of strings, here the named range 'Matbutiker', which requires me to put either 0 or the value in a new column using the following formula:

Please Login or Register  to view this content.

, populating the entire column for the entire range, A3:A24 and B3:B24, and then summing over that column. In my case I get entire E3:E24 full of irrelevant partial calculations.

What I really want to do is, since I'm only interested in the resulting sum and not the partials, is to put this condition in a SumIf formula to avoid having to spend and hide an entire column for this.

This is what I imagine I would be able to do:

Please Login or Register  to view this content.

If any of the entries in A3:A24 contains a substring of any of the strings in the named range Matbutiker, sum the corresponding value in B3:B24. Obviously the formula above doesn't work and I think the problem lies within the

Please Login or Register  to view this content.

, which can't handle arrays?

Can I somehow generate an array out of this condition so I can sum over it without explicitly having to do the partial calculations in another column? Please any hints or tips would be appreciated - I've wrestled with this for far too long!

Regards,
Daniel

[JohnTopley]

Try (Engish version!)

=SUMPRODUCT(((--ISNUMBER(SEARCH(Matbutiker,A3:A24)))>0)*(B3:B24))

Matches named range "Matbutiker" with A3:A34 and SUMS column B3:B34

OR

=SUMPRODUCT(((--ISNUMBER(SEARCH(Matbutiker,A3:A24)))*(B3:B24)))

[pkaff]

Try (Engish version!)

=SUMPRODUCT(((--ISNUMBER(SEARCH(Matbutiker,A3:A24)))>0)*(B3:B24))

Matches named range "Matbutiker" with A3:A34 and SUMS column B3:B34

OR

=SUMPRODUCT(((--ISNUMBER(SEARCH(Matbutiker,A3:A24)))*(B3:B24)))

Thanks for the reply!

I have in fact tried similar solutions already but they (the search function I guess) don't seem to work as I want them to with the array formula (ctrl + shift + enter). Does that particular formula work for you and have I just messed up syntax? Your suggestion gives me 0 sadly.

Regards,
Daniel

[JohnTopley]

It worked if I understood your requirement.

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[pkaff]

Thanks,

I don't think you've misunderstood the question and I also feel like it should work. See the attachment for clarification.

I don't know if there's a bug in my Excel or something because just now I got it to work for SOME cells . See if you can get the formulas to work in the sheet. It might be the Swedish version that's buggy?

Regards,
Daniel

[Phuocam]

Try this ...

=SUMPRODUCT(SUMIF(A2:A7,D11:D14,B2:B7))

[pkaff]

Try this ...

=SUMPRODUCT(SUMIF(A2:A7,D11:D14,B2:B7))

Hi, thanks for responding!

I think I forgot to mention that of course the "search" part of the formula is important since I'm interested in finding entries which contain the substring from the named range 'Matbutiker', or D11:D14 in your case.

Cheers,
Daniel

[JohnTopley]

Try

=SUMPRODUCT(((--ISNUMBER(SEARCH(TRANSPOSE(Matbutiker),A2:A24)))*(B2:B24)))

Enter with Ctrl+Shift+Enter

[Phuocam]

finding entries which contain the substring

=SUMPRODUCT(SUMIf(A2:A7,"*"&D11:D14&"*",B2:B7))

[pkaff]

=SUMPRODUCT(SUMIf(A2:A7,"*"&D11:D14&"*",B2:B7))

Yes, perfect this works! And it's so much simpler!! Thanks a lot!

Also thanks to you JohnTopley for trying to figure out the syntax for that other formula. I think that should work as well, but even with the transpose I get an error.

Cheers,
Daniel