question

KalaShaJahan-1078 avatar image
0 Votes"
KalaShaJahan-1078 asked emilyhua-msft commented

Need to add one more criteria to Sumproduct + Sumif formula

Good evening,
(was originally posted to Microsoft Answers, but was told it's an engineering questions, and should be posted here)

I am using the following formula on Tab01, Cell E5:

=SUMPRODUCT(SUMIF('Rolling Average Usages'!$F:$F,K5:L5,'Rolling Average Usages'!$L:$L))

*this formula returns the sum of all matches from the list in K5:L5. The math returns 12373 (which is correct).

102507-tab01.png

I would like to add the variable to include the value in cell B5 so that it returns just the values that correspond to it. the Tab02 picture shows the values, and the desired result should be 619+350=969.

102450-tab02.png

Help to add one more criteria would be appreciated. I can use sum(sumif1+sumif2+sumif3...), but that formula starts to grow pretty fast, so am looking to keep the formula smaller.

thank you.

K.


office-excel-itpro
tab01.png (13.2 KiB)
tab02.png (9.0 KiB)
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

@KalaShaJahan-1078
Please check whether Lz-3068's reply is helpful. The SUMIFS function could add all of its arguments that meet multiple criteria.


0 Votes 0 ·

1 Answer

Lz-3068 avatar image
2 Votes"
Lz-3068 answered Lz-3068 commented

Hi @KalaShaJahan-1078

with SUMIFS this should be:

 =SUMPRODUCT(
     SUMIFS('Rolling Average Usages'!$L:$L,
            'Rolling Average Usages'!$F:$F,K5:L5,
            'Rolling Average Usages'!$C:$C,B5
     )
 )

FYI I use Notepad++ to structure my formulas (as above). This really helps when they're long/complex


· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @KalaShaJahan-1078. How are things going with the above proposal?

0 Votes 0 ·