Count Times L is > M only when V is > W

[Risels7]

Hi,

I have four columns, L2 M2 V2 W2.

I'm trying to make it so it adds up the amount of times a value in L is larger then the value in M for the whole column, but also only counting when the value in V is larger then W, also for the whole column.

Can anybody work out the formula for me?

I have "=MAX(L2:L14654,M2:M14654)" and "(=MAX(V2:W14654,V2:W14654)"

But these only work out the largest value in the whole column.

Any help is appreciated.

[benishiryo]

hi Risels7. not really sure this is what you need, but it should help you get there:
=SUMPRODUCT(--(L2:L14654>M2:M14654))
this counts number of times L is larger than M

=SUMPRODUCT(--(L2:L14654>M2:M14654),--(V2:V14654>W2:W14654))
this counts number of times only when L is larger than M AND V is larger than W in the same row

[Risels7]

Perfect...

Thanks for that. Now all I need for this to be complete is to count the amount of times a name appears in column K AND L is larger then M.

Thank you

[Risels7]

Or Ideally...

count the amount of times a name appears in column K AND L is larger then M AND V is larger than W in the same row.

Thanks

[benishiryo]

what name? uploading a sample Excel file will enable us to help you better. to upload, press "Go Advanced" beside the "Post Quick Reply" button & click on the paperclip icon. ideally, it should contain your desired results

you can also try fiddling with the SUMPRODUCT. as long as you need 1 more logic, add in the comma, double negatives & the logical test with brackets. so if i were to guess, it'll be something like
=SUMPRODUCT(--(K2:K14654="Alex"),--(L2:L14654>M2:M14654),--(V2:V14654>W2:W14654))

i hope you can see a pattern