Not calculating

[Patcheen]

I have 2 columns 1 relies on the other to calculate

in column 1 M2:m10 formatted as general has the formula =IF(AND(L2="home",F2="Yc"),"1","")

in column Q2:Q10 formatted also as general is this formula =SUMPRODUCT(--(B$2:B2&E$2:E2=B2&E2)*(F2="YC")*(L2="Home"),M$2:M2)

my problem is if i type the number 1 manually into Column M then Column Q will calculate but not otherwise.

 

[awoohaw]

without your data it is hard to figure out if any suggested solution will be sufficient.
This is my attempt at editing your formula:

=SUMPRODUCT(--(B$2:B2=B2)*(E$2:E2=E2)*(F2="YC")*(L2="Home")*(M$2:M2))

Please use the xl2bb add in to post a mini worksheet.

 

[Patcheen]

Thank you. That only give s the result #Value!

 

[awoohaw]

post your data. Sanitize it if you need to.

 

[dreid1011]

I have 2 columns 1 relies on the other to calculate

in column 1 M2:m10 formatted as general has the formula =IF(AND(L2="home",F2="Yc"),"1","")

in column Q2:Q10 formatted also as general is this formula =SUMPRODUCT(--(B$2:B2&E$2:E2=B2&E2)*(F2="YC")*(L2="Home"),M$2:M2)

my problem is if i type the number 1 manually into Column M then Column Q will calculate but not otherwise.

It might be because your first formula is entering "1" as a text value instead of a number. Try this:

=IF(AND(L2="home",F2="Yc"),1,"")

 

[Patcheen]

Thank you this fixed it thank you so much for your help and to Awoohaw for his suggestion also. Thank you to the both of you

It might be because your first formula is entering "1" as a text value instead of a number. Try this:

=IF(AND(L2="home",F2="Yc
[/QUOTE]

 

[dreid1011]

Thank you this fixed it thank you so much for your help and to Awoohaw for his suggestion also. Thank you to the both of you

Glad that worked, and happy to help.