Why is this sumproduct formula not working?

0

I have a sheet that I need the greatest value between two columns, if they equal only count one and the number has to be >2.9 This is what I have so far:

=SUMPRODUCT((C13:C46<D13:D46)*(D13:D46>2.9))

The issue is it will only count column D even if the cell next to it in column C meets the requirements. The number in the picture should be 2 not 1.

Sheet Photo

Doug Greek

Posted 2016-08-24T14:59:33.970

Reputation: 1

The formula did not paste correct. =SUMPRODUCT((C13:C46D13:D46)*(D13:D462.9)) is what I have now.

– Doug Greek – 2016-08-24T15:00:18.023

Welcome to Super User. Please use the [edit] link below your question to update it instead of adding comments here. Also, this is a little difficult to understand. Please clarify things like how the data is laid out and whether this is Excel (on topic) or Google Spreadsheets (off topic). Reading thru [ask] can be helpful to understand how this site works. – CharlieRB – 2016-08-24T15:34:07.913

Answers

0

I believe this will return what you want:

=SUMPRODUCT(((C13:C46>2.9)+(D13:D46>2.9)>0)*1)

This will find if either is above 2.9 and only count 1 if both are.

Scott Craner

Posted 2016-08-24T14:59:33.970

Reputation: 16 128

@DougGreek please mark as correct by clicking the check mark by the answer. It is something only you can do. – Scott Craner – 2016-08-24T15:50:44.673

Asked: 2016-08-24T14:59:33.970

Viewed: 768 times

Active: 2016-08-24T15:40:37.463