IF OR function help

[reido113]

Hello All

I am trying to use the IF OR function while validating specific text in a column and am totally lost. Can anyone help me to understand this?

My goal is that if anything in column B1 to B5 = Yes or Maybe then the sum of the numbers in A1 to A5 would be returned for the rows that have Yes or Maybe and not the ones with No in them.

Here is the function that I thought would work however it only returns #Value
=IF(OR(B1:B5="Yes",B1:B5="Maybe"),SUM(A2:A5))

*Side not for my learnings; I have successfully been able to do this with only 1 condition using the following function. However I do not understand why the * is needed before and after the text inside the quotation marks. But really I need the OR function here if feasible.
=SUMIF(B1:B5,"*Rotating*",A1:A5)

Cheers,

Reid

[daddylonglegs]

The *s are "wildcards" so in your SUMIF

=SUMIF(B1:B5,"*Rotating*",A1:A5)

that will sum A1:A5 when the corresponding row contains "rotating" (possibly within other text), rather than being exactly equal to "rotating". For an OR effect try this version

=SUM(SUMIF(B1:B5,{"Yes","Maybe"},A1:A5))

[Andrew-R]

This one is much trickier than you may think, for a number of reasons, but this should work:

=SUMPRODUCT(A1:A5,--(LEN(B1:B5)>2))

[Fotis1991]

Hi

Try this.

=SUMPRODUCT((B2:B5="YES")*(C2:C5="Maybe")*(A2:A5))

[Andrew-R]

=SUMPRODUCT((B2:B5="YES")*(C2:C5="Maybe")*(A2:A5))

Where's column C come from? You can't multiply together the Booleans for "Yes" and "Maybe", because if you do it will only sum rows where column B is both "Yes" and "Maybe".

[reido113]

Thanks Y'all. I breath a sigh of relief!
daddylonglegs Thanks for the explanation of the wildcards here. And your function worked Perfectly! I have now found a use for squiggles too

[daddylonglegs]

My SUMIF was explicitly summing for "Yes" and Maybe" - if that's the same as summing when not equal to "No" then you can just do that

=SUMIF(B1:B5,"<>No",A1:A5)

[reido113]

I see. In my actual deeper case there are many more combinations of texts to consider.
...should that actually be <>"no" ?

*edit* I should have tried first, sorry. Disregard and Thanks Again. This I am sure will come in handy.

Cheers

[Fotis1991]

@Andrew-R

...Where's column C come from? You can't multiply together the Booleans for "Yes" and "Maybe", because if you do it will only sum rows where column B is both "Yes" and "Maybe".

Column C, came from nowhere....! I ...just show column C, in first OP question.....

..And, Andrew, please,my knowdledge is not so bad, to don't let me to know, what is a simple SUMPRODUCT, does...

Yes, i did not understand the question, just my answer was wrong... ...Just this.