Include and exclude items using SUMIFS and wildcards

1. Include and exclude items using SUMIFS and wildcards

Hi.
There are two questions inherent to this thread. But let's go step-wise:

I have this SUMIFS formula in which I want to include items (Description) that contains ANY of the words in cell C16 and C17:
= SUMIFS(Value; Description; "*"& C16 &"*"; Description; "*"& C17 &"*")

Unfortunately, only the items that contain BOTH words are being included in the sum.
Any idea on how to get around this?

2. Re: Include and exclude items using SUMIFS and wildcards

The only problem with your formula is that the items that include BOTH words are being doubly added to the sum.
Thus, the result is 6.750 instead of 3.650.

It will be brilliant if the "double add" is eliminated.

3. Re: Include and exclude items using SUMIFS and wildcards

You can use this version

=SUMPRODUCT(SUMIFS(Value;Description;"*"& C16:C17 &"*"))

...but that will double count any rows that contain both....so if you don't want that you can subtract the result of the first formula which only counts both

4. Re: Include and exclude items using SUMIFS and wildcards

That certainly works.

But the criteria fields will grow much larger and resorting to the subtraction of the two formulas will prove very fatiguing, resulting in a relatively intricate and long formula.

Therefore, a more compact method is required.

5. Re: Include and exclude items using SUMIFS and wildcards

The following array entered formula* yields the expect 3650 for the sample data given:
Formula:
`Please Login or Register  to view this content.`
*Array entered formulas are activated by simultaneously pressing the Ctrl, Shift and Enter keys while the cell containing the formula is in edit mode.
Edit: The array entered formula may also be written as:
Formula:
`Please Login or Register  to view this content.`

Let us know if you have any questions.

6. Re: Include and exclude items using SUMIFS and wildcards

Worked wonderfully, JeteMc.
Thanks!

Would it be possible now to accommodate the second set of criteria?
This means that based on the previous calculations, we're now only counting:
• The items that correspond to 11401* ("Analytical Center" begins with...), and;
• The items that correspond to 6511* ("Depreciation Account" begins with...)?

This will of course, require the use of wildcards which are kinda tricky.
Tried to tweak your formula myself, but without success.

7. Re: Include and exclude items using SUMIFS and wildcards

Try the following array entered formula* (see post #5 for activation):
Formula:
`Please Login or Register  to view this content.`
Let us know if you have any questions.

8. Re: Include and exclude items using SUMIFS and wildcards

That will do, Jete.
And I won't even bother you with the inclusion of other criteria. It has become quite clear that the formula will become so complex, that I'd rather use a combination of formulas to get the final result.

As for your suggestions, I certainly learned a lot from it.
Big thanks for that!

9. Re: Include and exclude items using SUMIFS and wildcards

You're Welcome and thank you for the feedback. I think that you are wise to break the final result into multiple formulas. Smaller formulas are easier to trouble shoot and according to "some sources" run faster. Please take a moment to mark the thread as 'Solved' using the thread tools link above your first post. I hope that you have a blessed day.

10. Re: Include and exclude items using SUMIFS and wildcards

Originally Posted by JeteMc
=SUMPRODUCT(IF(IF(IFERROR(SEARCH(C16,B6:B11),FALSE),1,0)+(IF(IFERROR(SEARCH(C17,B6:B11),FALSE),1,0))>0,1,0),E6:E11)
Hi JeteMc,

If you do this slightly differently (without IFs) then you can use a version which doesn't need to be "array entered", i.e.

=SUMPRODUCT((ISNUMBER(SEARCH(C16,B6:B11))+ISNUMBER(SEARCH(C17,B6:B11))>0)+0,E6:E11)

11. Re: Include and exclude items using SUMIFS and wildcards

By extension a regular formula that incorporates the second set of inclusions could be written:
Formula:
`Please Login or Register  to view this content.`

There are currently 1 users browsing this thread. (0 members and 1 guests)