show blank if 0

[Mattlake]

Hi

I stupid question I have the below formula (which works as i need it)

=SUMPRODUCT(--('2023'!$I$2:$I$398>=Totals!$C$2),--('2023'!$I$2:$I$398<=Totals!$C$3),--('2023'!$B$2:$B$398=Totals!A5)*('2023'!$J$2:$J$398=other!$N$5))

I need it to show blank or a - if it is Zero

is there a simple way to do this?

Regards
Matthew

 

[nemmi69]

Try

if(=SUMPRODUCT(--('2023'!$I$2:$I$398>=Totals!$C$2),--('2023'!$I$2:$I$398<=Totals!$C$3),--('2023'!$B$2:$B$398=Totals!A5)*('2023'!$J$2:$J$398=other!$N$5))=0,"",=SUMPRODUCT(--('2023'!$I$2:$I$398>=Totals!$C$2),--('2023'!$I$2:$I$398<=Totals!$C$3),--('2023'!$B$2:$B$398=Totals!A5)*('2023'!$J$2:$J$398=other!$N$5)))

 

[Fluff]

How about

=IFERROR(1/(1/SUMPRODUCT(--('2023'!$I$2:$I$398>=Totals!$C$2),--('2023'!$I$2:$I$398<=Totals!$C$3),--('2023'!$B$2:$B$398=Totals!A5)*('2023'!$J$2:$J$398=other!$N$5))),"")

 

[Mattlake]

thank you .

how you all remember this detail

 

[nemmi69]

20+ years of playing with Excel

 

[Fluff]

Glad we could help & thanks for the feedback.