# Sum with 5 Conditions in Visible Rows of a Table

1. ## Sum with 5 Conditions in Visible Rows of a Table

Hello,

I'm having trouble making a correct formula to sum up some values in Table1 based on multiple criteria and only in visible rows.
The formula serves as a verification for some data I am reporting and, of course, I need to be sure it is correct.

I tried various formulas by using SUMPRODUCT, SUBTOTAL, AGGREGATE, but nothing worked.

See the attached file with my table of data (Table1), Nm that must be excluded from the sum (H2:H6), desired results obtained with SUMIFS (G2:G3), main slicer (Mt), helper slicer (Nm) and not working formula (D1).

Hopefully someone can help me find a formula that would work.

2. ## Re: Sum with 5 Conditions in Visible Rows of a Table

Hi,

It's generally preferable to avoid a mixture of structured table and standard referencing:

=SUMPRODUCT(SUBTOTAL(109,OFFSET(INDEX(Table1[Qty],1),ROW(Table1[Qty])-MIN(ROW(Table1[Qty])),)),1-ISNUMBER(MATCH(Table1[Nm],H2:H6,0)))

Regards

3. ## Re: Sum with 5 Conditions in Visible Rows of a Table

Thanks, that works great for my sample file and also works if the values in Nm match the values in H2:H6 exactly.
In my real data however there are partial matches - the AB, CD, EF etc. are just 2 characters of longer strings, eg. XXXXABXXXX or CDZZZZZZ.

Is it possible to make this to exclude partial matches like in my original-non-working formula?
Something like "*" & \$H\$3 & "*" but I can work without ranges, like for example "*AB*", "*CD*"

Because the values I have in column Nm in the real data are long and sometimes letters are missing from the entire name, but they contain certain strings for sure - eg. the string RNG or TXT somewhere in the entire name in column Nm.

4. ## Re: Sum with 5 Conditions in Visible Rows of a Table

Then I believe you'll need an array formula**:

=SUM(IF(SUBTOTAL(102,OFFSET(INDEX(Table1[Qty],1),ROW(Table1[Qty])-MIN(ROW(Table1[Qty])),)),IF(MMULT(0+ISNUMBER(SEARCH(TRANSPOSE(H2:H6),(Table1[Nm]))),ROW(H2:H6)^0)=0,Table1[Qty])))

Regards

**Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).

5. ## Re: Sum with 5 Conditions in Visible Rows of a Table

This works great, thank you very much.

I still have soooo so much to learn.

Best regards.

Edit:
Wanted to give rep for both posts because both were great solutions.
Didn't know I had to "spread some more" before being able to give more to you, so I wasn't able to do it yet.
Will do in the future, hopefully I remember.

6. ## Re: Sum with 5 Conditions in Visible Rows of a Table

You're welcome!

(And don't worry about the rep points. I'm more than happy with what you've given! Cheers!)

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