How can i used nested SUM functions with multiple criteria?

Question

This formula is nearly complete, I am just missing a small part of it that I cannot see!

I want cell A19 to SUM column K:K if column A:A contains either OPEN OR CLOSED.

What is it I am missing?

The formula works if only counting CLOSED or OPEN however I need it to count both.

The current formula in A19 :

=IF(COUNT(A9)=1,TEXT(SUMIF('SPREADS LOG'!A:A,("CLOSED"),'SPREADS LOG'!K:K)," £ #,##0.00")&" / "&TEXT(SUM(SUMIF('SPREADS LOG'!A:A,{"CLOSED","OPEN"},'SPREADS LOG'!K:K)," £ #,##0.00"),""))

Note I need to keep the oddities of / in the formula as it is doing two separate calculations and I would like them to be displayed as 1234 / 5678

One time it is worth checking the parts before assembling into one long function. — Solar Mike, Apr 01 '21 at 13:43
Have edited it to ```=IF(COUNT(A9)=1,TEXT(SUMIF('SPREADS LOG'!A:A,("CLOSED"),'SPREADS LOG'!K:K)," £ #,##0.00")&" / "&TEXT(SUM(SUMIF('SPREADS LOG'!A:A,{"CLOSED","OPEN"},'SPREADS LOG'!K:K)," £ #,##0.00"),""))``` however now shows VALUE error, any further advice? — Insider, Apr 01 '21 at 13:43

score 2 · Accepted Answer · edited Apr 02 '21 at 00:32

This not so elegant way worked for me ...

=TEXT(SUMIF('SPREADS LOG'!A:A,("CLOSED"),'SPREADS LOG'!K:K)," £ #,##0.00")&
" / "&
TEXT(SUM(
         SUMIF('SPREADS LOG'!A:A,"Open",'SPREADS LOG'!K:K),
         SUMIF('SPREADS LOG'!A:A,"Closed",'SPREADS LOG'!K:K)
         )," £ #,##0.00")

Are there values in K other than Open or Closed? If not, you could replace lines 4 and 5 with SPREADS LOG'!A:A.

Assuming that there are other values in K and that you have a 365 subscription, you can use the new LET function to make this formula a bit simpler.

=LET(sumRng,'SPREADS LOG'!K:K, lkupRng,'SPREADS LOG'!A:A,
           TEXT(SUMIF(lkupRng,("CLOSED"),sumRng)," £ #,##0.00")
           &" / "&
           TEXT(SUM(
                    SUMIF(lkupRng,"Open",sumRng),
                    SUMIF(lkupRng,"Closed",sumRng)
                   )," £ #,##0.00")
    )

How can i used nested SUM functions with multiple criteria?

1 Answers1