I am having problems with a IF(AND(OR nested function

D

Debbie

it all works except for H6>=6,I6>=6,J6>=6,K6>=6,L6>=6 in each if
statement. Sometimes these cells are blank; therefore, there is no
calculation, but it must calculate if a number >=6 is in this cell

=IF(AND(M6>29.9,F6<19),32*5,IF(AND(M6>29.9,F6>=19),
30*5,IF(AND(M6<30,F6<19,H6>=6,I6>=6,J6>=6,K6>=6,L6>=6),
39*S6,IF(AND(M6<30,F6<19,H6>=6,I6>=6,J6>=6,K6>=6),
39*S6,IF(AND(M6<30,H6<6,I6<6,J6<6,K6<6),26*S6,0)))))

Can anyone help me figure this out. Thank you so much in advance!
 
D

Debbie

it all works except for H6>=6,I6>=6,J6>=6,K6>=6,L6>=6 in each if
statement. Sometimes these cells are blank; therefore, there is no
calculation, but it must calculate if a number >=6 is in this cell
=IF(AND(M6>29.9,F6<19),32*5,IF(AND(M6>29.9,F6>=19),
30*5,IF(AND(M6<30,F6<19,H6>=6,I6>=6,J6>=6,K6>=6,L6>=6),
39*S6,IF(AND(M6<30,F6<19,H6>=6,I6>=6,J6>=6,K6>=6),
39*S6,IF(AND(M6<30,H6<6,I6<6,J6<6,K6<6),26*S6,0)))))

Can anyone help me figure this out. Thank you so much in advance!

My problem is in cell H6, I6, J6, K6, L6 i need to calculate $39 x
how many cells have >=6 or if it is <6 then it needs to multiply $26
X how many cells have <6 and then total (39*?)+(26*?) = total

Hope this makes sense!
 
M

Max

My problem is in cell H6, I6, J6, K6, L6 i need to calculate $39 x
how many cells have >=6 or if it is <6 then it needs to multiply $26
X how many cells have <6 and then total (39*?)+(26*?) = total

Going by the above, try:
=SUMPRODUCT(--(H6:L6>=6),H6:L6*39)+SUMPRODUCT(--(H6:L6<6),H6:L6*26)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top