SOLVED

# SumIf and Subtotal with Multiple Criteria

Steel Contributor

# SumIf and Subtotal with Multiple Criteria

Hello Experts,

I have a table that is filtered.

I need to sum the visible cells only

However, the formula I am using (sumif) sums all the data so it sums the visible and non visible.

I know that subtotal will work but I need to add critiria to it.

The Criteria is sum visible cells if between \$x and \$y

I hope that makes sense.

I think the file is clearer.  Please take a look.

Let me know if you have any questions.

thank you

2 Replies
best response confirmed by Tony2021 (Steel Contributor)
Solution

# Re: SumIf and Subtotal with Multiple Criteria

For example:

=SUMPRODUCT(SUBTOTAL(3,OFFSET(DDProposalFinal[Vendor],ROW(DDProposalFinal[Vendor])-MIN(ROW(DDProposalFinal[Vendor])),,1)),( DDProposalFinal[USD Amount]>\$B\$13)*(DDProposalFinal[USD Amount]<=\$B\$14)*DDProposalFinal[USD Amount])

# Re: SumIf and Subtotal with Multiple Criteria

Amazing. that worked perfectly! thank you very much Hans. have a good day.
1 best response

Accepted Solutions
best response confirmed by Tony2021 (Steel Contributor)
Solution

# Re: SumIf and Subtotal with Multiple Criteria

For example:

=SUMPRODUCT(SUBTOTAL(3,OFFSET(DDProposalFinal[Vendor],ROW(DDProposalFinal[Vendor])-MIN(ROW(DDProposalFinal[Vendor])),,1)),( DDProposalFinal[USD Amount]>\$B\$13)*(DDProposalFinal[USD Amount]<=\$B\$14)*DDProposalFinal[USD Amount])