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

 

[msiegal]

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

So there's a column D that I am looking to count from which lists store numbers for survey candidates this list includes numbers like: 4906, 2218, 8560, 6620, 3309, 4941, 01A1, 01A2, 01RM, 02RO, 07RE..... (As more people complete the survey we will continue to add data to the file so I need to observe everything within the column D:D

Then, there is another sheet (Sheet1) that lists division name, other details for individual stores, and the store "number" for each store (store number is in column G).

So, the store numbers within the 'Northern Division' range from 1549-4285 and they are all numbers, then there is the southern division which ranges from 4409-7037, and they are also all numbers, and then there is the western division that has a number range from 7158-9555 but it also includes about 300 stores where the "store number" is a combination of letters and numbers....

So instead of using the formulas I used for the northern division, for example, which just includes numbers:

=COUNTIF('Paste Key Survey Results'!D:D,">1549")-COUNTIF('Paste Key Survey Results'!D:D,">=4285")
OR
=SUMPRODUCT(--('Paste Key Survey Results'!J:J="Very Poor"),--('Paste Key Survey Results'!D:D>1549),--('Paste Key Survey Results'!D:D<4285))

I wanted to use a countif statement where I could highlight the corresponding store numbers for all stores within the western division.. when I do this the could becomes 0, which is not correct.

This is what I tried but it does not work
=COUNTIF('Paste Key Survey Results'!D:D, Sheet1!G1495:G1991)

Sorry this is so confusing.. if you need further clarification please let me know!!

 

[Aladin Akyurek]

Please try to provide a scaled-down sample along with the results that must obtain.

 

[msiegal]

Please try to provide a scaled-down sample along with the results that must obtain.

'Paste Key Survey Results' column D includes the following candidate store numbers: 1, 3, 7, 10, 14, 19, 25, 27, 30, 38, 1A, 1AB, 2C, 2F, 2RB

'Sheet1' column G includes all Store#s for western division: 20, 21, 22, 24, 25, 27, 28, 29, 30, 32, 33, 35, 36, 37, 39, 40, 1A, 1B, 1AB, 2A, 2BC, 2C, 2DE, 2F, 2RB, 3AC, 3D, 4DE

count all the items in 'Paste Key Survey Results' column D that are in 'Sheet1' column G

 

[Aladin Akyurek]

'Paste Key Survey Results' column D includes the following candidate store numbers: 1, 3, 7, 10, 14, 19, 25, 27, 30, 38, 1A, 1AB, 2C, 2F, 2RB

'Sheet1' column G includes all Store#s for western division: 20, 21, 22, 24, 25, 27, 28, 29, 30, 32, 33, 35, 36, 37, 39, 40, 1A, 1B, 1AB, 2A, 2BC, 2C, 2DE, 2F, 2RB, 3AC, 3D, 4DE

count all the items in 'Paste Key Survey Results' column D that are in 'Sheet1' column G

And what is the result for store 1 of Paste Key Survey Results, given the data in column G of Sheet1?

 

[msiegal]

And what is the result for store 1 of Paste Key Survey Results, given the data in column G of Sheet1?

the count should be 8

 

[Aladin Akyurek]

the count should be 8

How does 1 exist in

20, 21, 22, 24, 25, 27, 28, 29, 30, 32, 33, 35, 36, 37, 39, 40, 1A, 1B, 1AB, 2A, 2BC, 2C, 2DE, 2F, 2RB, 3AC, 3D, 4DE

8x times?

 

[msiegal]

How does 1 exist in

20, 21, 22, 24, 25, 27, 28, 29, 30, 32, 33, 35, 36, 37, 39, 40, 1A, 1B, 1AB, 2A, 2BC, 2C, 2DE, 2F, 2RB, 3AC, 3D, 4DE

8x times?

I think I need to utilize V-lookups because i'm having trouble explaining this and I may be using the Countif function for too complex of a dataset

 

[Aladin Akyurek]

I think I need to utilize V-lookups because i'm having trouble explaining this and I may be using the Countif function for too complex of a dataset

What is so difficult about saying which G values count as store 1? I'm not asking you to provide a formula...

 

[msiegal]

what I am trying to do is count the number of values in column D that match any value within the "western division" range in column G... Column G is all encompassing of the possible stores that there are (here i just gave the example as if all the stores in the list are for the western division, on my actual dataset column G also includes store numbers for all the southern and the northern divisions as well, there is also a column C that has the name of the corresponding division for the store number). Column D is a list of the store numbers for candidates who have completed the survey. So, I want to count how many people who have completed the survey did so for a store in the western division (...as well as the southern division, and the northern division).... Does this make sense? should I use a pivot table? or just something else not a count formula?

 

[msiegal]

The store numbers in the list of all the stores are not in any numerical order, the store# really needs to be treated as a unique name that identifies the store, and the division in which that store fits, rather than a numerical value. the only information in the survey responses is the store number where the individuals conducted their interviews