Complex, Countif 2 conditions met

[msiegal]

Hi All,

I am looking to combined these two countif statements below to get a new =countif(Statement1 AND Statement2), I can't seem to figure out a formula that will work.. is this possible? Or is there a better way to do this?

Statement 1 =COUNTIFS('Paste Key Survey Results'!F:F, "Yes")
Statement 2 =COUNTIF('Paste Key Survey Results'!D:D,">119")-COUNTIF('Paste Key Survey Results'!D:D,">=2603")

Any and all advice is appreciated!

Thanks,
Marly

 

[JackDanIce]

Welcome to the board. Try:

=SUMPRODUCT(--('Paste Key Survey Results'!F:F = "Yes"),--('Paste Key Survey Results'!D:D > 119),--('Paste Key Survey Results'!D:D < 2603)

 

[msiegal]

Welcome to the board. Try:

=SUMPRODUCT(--('Paste Key Survey Results'!F:F = "Yes"),--('Paste Key Survey Results'!D:D > 119),--('Paste Key Survey Results'!D:D < 2603)

Awesome, this worked, thanks so much!!

 

[JackDanIce]

You're welcome.

 

[Aladin Akyurek]

Does this work for you?

=COUNTIFS(
   'Paste Key Survey Results'!F:F,"Yes",
   'Paste Key Survey Results'!D:D,">119",
   'Paste Key Survey Results'!D:D,"<2603")

<strike></strike>

 

[msiegal]

Thank you both for your input, it was very helpful. I have another related, but different question. I am wondering if you can do something like COUNTIF('Paste Key Survey Results'!D:D, 'Sheet1'!Range of numbers in column X).. this hasn't worked for me but I feel like there should be a way to reference a range of numbers within a column? I pretty much lets say I want to count if a number in column D is equal to a number in column X2-X500, is there a way to do this?

 

[Aladin Akyurek]

Thank you both for your input, it was very helpful. I have another related, but different question. I am wondering if you can do something like COUNTIF('Paste Key Survey Results'!D:D, 'Sheet1'!Range of numbers in column X).. this hasn't worked for me but I feel like there should be a way to reference a range of numbers within a column? I pretty much lets say I want to count if a number in column D is equal to a number in column X2-X500, is there a way to do this?

What does "a number in column X2-X500" mean?

 

[msiegal]

What does "a number in column X2-X500" mean?

Column X is all store numbers and the column is broken down into 3 divisions, division 1 goes from X2 (Column X, Row 2) until X500, division 2 goes from X501-X1000 and division 3 goes from X1001-1500... is there a way to just highlight the range of numbers within that area to say count if the number appears withing this area?

 

[msiegal]

Column X is all store numbers and the column is broken down into 3 divisions, division 1 goes from X2 (Column X, Row 2) until X500, division 2 goes from X501-X1000 and division 3 goes from X1001-1500... is there a way to just highlight the range of numbers within that area to say count if the number appears withing this area?

P.S. There are also some items that are not actually numbers like the store number is 15RB or something... can I treat the column as "Text" rather than "number" so that I can select the area needed and be able to identify items like words rather than numbers?

 

[Aladin Akyurek]

P.S. There are also some items that are not actually numbers like the store number is 15RB or something... can I treat the column as "Text" rather than "number" so that I can select the area needed and be able to identify items like words rather than numbers?

Try to provide a sample, representative sample along with the result(s) that must obtain.