If column X is YES and column Y has value return 1

[madjan]

Hi everyone,

I would like some help on an issue I've got with my spreadsheet in Excel.

If cell B5 in worksheet X has value YES it should check if I5 has value A, B or C and then return 1. Then check the next row down to B100 -> I100 (it can be a static value since it won't ever be over 100 rows).
It should then sum up all the 1s and return that number to another worksheet.

Is this possible to do with a formula or would I have to go with VB?


Thanks in advance!

[Andrew-R]

So you're looking to do the whole thing in one formula?

How about:

=SUMPRODUCT(--(B5:B100="YES"),--(ISNUMBER(FIND(I5:I100,"ABC"))))

[martindwilson]

could you put that in a workbook and attach it here it's not clear what you are asking

[TMS]

One way:

=IF(AND(B5="yes",OR(I5="a",I5="b",I5="c")),1,"")

Regards, TMS

[madjan]

could you put that in a workbook and attach it here it's not clear what you are asking

Sure mate! Look attachment.

[Andrew-R]

OK, on you summary sheet take the spaces out of the sheet names in column A (i.e. change "Sheet 1" to "Sheet1") and then try this formula in column C:

=SUMPRODUCT(--(INDIRECT("'" & A4 & "'!B5:b100")="YES"),--(ISNUMBER(FIND("~" & INDIRECT("'" & A4 & "'!i5:i100") & "~","~A~B~C~"))))

[madjan]

OK, on you summary sheet take the spaces out of the sheet names in column A (i.e. change "Sheet 1" to "Sheet1") and then try this formula in column C:

=SUMPRODUCT(--(INDIRECT("'" & A4 & "'!B5:b100")="YES"),--(ISNUMBER(FIND("~" & INDIRECT("'" & A4 & "'!i5:i100") & "~","~A~B~C~"))))

I really can't get this to work. The "," between INDIRECT and ISNUMBER isn't likeable by Excel and gives me error.
Just to make all clear from my example file above: the cell C4 in Summary should present the result of Sheet1, which is 3 and the cell C5 in Summary should present the result of Sheet2, which is one.

[TMS]

Could be your regional settings. Try changing it to a semi-colon (