Extra IF condition required

[ollyhughes1982]

Hi,

I have the below formula in my workbook and want to add in another IF statement condition, so that the formula also only counts values where a condition is met in column D of the 'All Completed Runs' worksheet. The condition that column D in the 'All Completed Runs' needs to meet is “United Kingdom”.

=IF('All Completed Runs'!B3="","",SUMPRODUCT(('All Completed Runs'!BW3:BW2002<>"")/COUNTIF('All Completed Runs'!BW3:BW2002,'All Completed Runs'!BW3:BW2002&"")))

I have tried adding in an IF statement just before the SUMPRODUCT, but this doesn’t work.

Thanks in advance!

 

[RasGhul]

If the condition only needs to be in the Count?

Maybe try,

=IF('All Completed Runs'!B3="","",SUMPRODUCT(('All Completed Runs'!BW3:BW2002<>"")/COUNTIFS('All Completed Runs'!BW3:BW2002,'All Completed Runs'!BW3:BW2002&"",'All Completed Runs'!D3:D2002,"United Kingdom")))

 

[Peter_SSs]

1. What worksheet name is this formula in?
2. In 'All Completed Runs', is the data in column BW numeric, text or some of each?

Instead of your existing formula, I think this should do the same thing

=IF('All Completed Runs'!B3="","",ROWS(UNIQUE(FILTER('All Completed Runs'!BW3:BW2002,'All Completed Runs'!BW3:BW2002<>""))))

.. and with the extra condition

=IF('All Completed Runs'!B3="","",ROWS(UNIQUE(FILTER('All Completed Runs'!BW3:BW2002,('All Completed Runs'!BW3:BW2002<>"")*('All Completed Runs'!D3:D2002="United Kingdom")))))

 

[ollyhughes1982]

1. What worksheet name is this formula in?
2. In 'All Completed Runs', is the data in column BW numeric, text or some of each?

Instead of your existing formula, I think this should do the same thing

=IF('All Completed Runs'!B3="","",ROWS(UNIQUE(FILTER('All Completed Runs'!BW3:BW2002,'All Completed Runs'!BW3:BW2002<>""))))

.. and with the extra condition

=IF('All Completed Runs'!B3="","",ROWS(UNIQUE(FILTER('All Completed Runs'!BW3:BW2002,('All Completed Runs'!BW3:BW2002<>"")*('All Completed Runs'!D3:D2002="United Kingdom")))))

Brilliant, this worked perfectly. Thanks!

 

[Peter_SSs]

Brilliant, this worked perfectly. Thanks!

That's good, but what about the answers to my 2 questions which could possibly allow something even simpler?