Count If with multiple criteria and unique values

[dixon1983]

Hi,
I dont know if this is possible or not but thought I'd ask anyway.

I have a sheet of data and want to run a count on the values in Column A with the following criteria:

- If the value in column B is repeated then the value in column A can only be counted once...
- Column C must equal 'M'.


Example:
A.....B.....C
1.....42....M
1.....42....M
2.....41....A
2.....41....M
1.....56....M


In this example 1 would be counted 2 times, and 2 would be counted once.

I have about 15 sheets of data like this and would like a master sheet to look into each sheet and give me a count of each so id prefer to not use the advanced filter => unique variables option if possible.

Any help would be greatly appreciated.

Dixon.

 

[juicbox]

The range is actually Columns F through S with indefinite rows. I am tracking orders with various issues within a years time so there could be tons. The helper column seems to have remedied the issue for now so hopefully it will stay working. I greatly appreciate all of your help.

 

[Ohioste]

I've got a similar problem with what I can tell is an added complexity. I'm using SQL to pull data into a tab on a spreadsheet and trying to report the informaiton gathered onto a separate tab.

I'm trying to distinct count two scenarios. In the below data I'd like to be able to do a distinct count when a carton without a Tote # when "MHE TRANSLATED" = "TWR1EXT2" and when a tote is in the same location. The below example should give me a total of 2(1 carton and 1 tote). Any help would be much apprecaited.
[TABLE="width: 511"]
<TBODY>[TR]
[TD]CARTON[/TD]
[TD]STATUS[/TD]
[TD]TOTE_#[/TD]
[TD]MHE Loc[/TD]
[TD]MHE Translated[/TD]
[/TR]
[TR]
[TD]00020000000107705479[/TD]
[TD] 04[/TD]
[TD][/TD]
[TD]SHIPJP[/TD]
[TD]SHIPJP[/TD]
[/TR]
[TR]
[TD]00020000000107708050[/TD]
[TD] 04[/TD]
[TD][/TD]
[TD]TWR1EXT2[/TD]
[TD]TWR1EXT2[/TD]
[/TR]
[TR]
[TD]00020000000107708050 [/TD]
[TD] 04[/TD]
[TD][/TD]
[TD]TWR1EXT2[/TD]
[TD]TWR1EXT2[/TD]
[/TR]
[TR]
[TD]00020000000107714907[/TD]
[TD] 04[/TD]
[TD]T5000984[/TD]
[TD]TWR1EXT2[/TD]
[TD]TWR1EXT2[/TD]
[/TR]
[TR]
[TD]00020000000107714907[/TD]
[TD] 04[/TD]
[TD]T5000984[/TD]
[TD]TWR1EXT2[/TD]
[TD]TWR1EXT2[/TD]
[/TR]
[TR]
[TD]00020000000107714907 [/TD]
[TD] 04[/TD]
[TD]T5000984[/TD]
[TD]TWR1EXT2[/TD]
[TD]TWR1EXT2[/TD]
[/TR]
[TR]
[TD]00020000000107715775[/TD]
[TD] 04[/TD]
[TD]T5000984[/TD]
[TD]TWR1EXT2[/TD]
[TD]TWR1EXT2[/TD]
[/TR]
[TR]
[TD]00020000000107715775[/TD]
[TD] 04[/TD]
[TD]T5000984[/TD]
[TD]TWR1EXT2[/TD]
[TD]TWR1EXT2[/TD]
[/TR]
[TR]
[TD]00020000000107715775 [/TD]
[TD] 04[/TD]
[TD]T5000984[/TD]
[TD]TWR1EXT2[/TD]
[TD]TWR1EXT2[/TD]
[/TR]
</TBODY><COLGROUP><COL><COL><COL><COL><COL></COLGROUP>[/TABLE]

 

[Ohioste]

So after a few hours, I'm a step closer. This formula will at least count my totes that are in a particular area. Now I need to determine how to distinct count if the tote is blank and I have cartons.

Here's the formulate I used:

=SUM(IF(FREQUENCY(IF(Table_ExternalData_4[TOTE_'#]<>"",IF(Table_ExternalData_4[MHE Translated]="TWR1EXT2",
    MATCH("~"&Table_ExternalData_4[TOTE_'#],Table_ExternalData_4[TOTE_'#]&"",0))),
       ROW(Table_ExternalData_4[[#All],[TOTE_'#]])-ROW('Data Pull'!AQ5)+1),1))

 

[Aladin Akyurek]

The lay-out you posted does not correspond to the the input data.

Are you wanting to create a unique list of customers on the monthly report with respect to the month April and count how often each appear in that month?

Morning Aladin.
Correct

...

Still not sure what you are after. The best thing to do is to create a small sample along with the desired results.

 

[Ohioste]

Any idea on the below issue?

So after a few hours, I'm a step closer. This formula will at least count my totes that are in a particular area. Now I need to determine how to distinct count if the tote is blank and I have cartons.

Here's the formulate I used:

=SUM(IF(FREQUENCY(IF(Table_ExternalData_4[TOTE_'#]<>"",IF(Table_ExternalData_4[MHE Translated]="TWR1EXT2",
    MATCH("~"&Table_ExternalData_4[TOTE_'#],Table_ExternalData_4[TOTE_'#]&"",0))),
       ROW(Table_ExternalData_4[[#All],[TOTE_'#]])-ROW('Data Pull'!AQ5)+1),1))

 

[Aladin Akyurek]

Still not sure what you are after. The best thing to do is to create a small sample along with the desired results.

Any idea on the below issue?

Try to produce a convincing sample for which such formula would be approipriate...

 

[Ohioste]

Try to produce a convincing sample for which such formula would be approipriate...

Aladin, first I appreciate the help. You seem to have helped a ton of people on this.

In post 182, I listed a small sample and what the expected results would be. I was able to get it to start counting the distinct number of totes that had my desired results and the next step would be to look at tote and if it's blank count the number of cartons.

=SUM(IF(FREQUENCY(IF(Table_ExternalData_4[TOTE_'#]<>"",IF(Table_ExternalData_4[MHE Translated]="TWR1EXT2",
MATCH("~"&Table_ExternalData_4[TOTE_'#],Table_ExternalData_4[TOTE_'#]&"",0))),
ROW(Table_ExternalData_4[[#All],[TOTE_'#]])-ROW('Data Pull'!AQ5)+1),1))

So essentially, I want to distinct count the number of totes and add that to the distinct count of cartons without totes based on column MHE Translated.

 

[Aladin Akyurek]

Aladin, first I appreciate the help. You seem to have helped a ton of people on this.

In post 182, I listed a small sample and what the expected results would be. I was able to get it to start counting the distinct number of totes that had my desired results and the next step would be to look at tote and if it's blank count the number of cartons.

=SUM(IF(FREQUENCY(IF(Table_ExternalData_4[TOTE_'#]<>"",IF(Table_ExternalData_4[MHE Translated]="TWR1EXT2",
MATCH("~"&Table_ExternalData_4[TOTE_'#],Table_ExternalData_4[TOTE_'#]&"",0))),
ROW(Table_ExternalData_4[[#All],[TOTE_'#]])-ROW('Data Pull'!AQ5)+1),1))

So essentially, I want to distinct count the number of totes and add that to the distinct count of cartons without totes based on column MHE Translated.

Here are some calcs. Did not understand all what you require. If you allow me to say so, try not to use/attempt an abstract language when you require help. Rather, try to be maximally concrete.

[Table="width:, class:grid"][tr][td]Row\Col[/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][td]

D​

[/td][td]

E​

[/td][/tr]
[tr][td]

1​

[/td][td]CARTON[/td][td]STATUS[/td][td]TOTE_#[/td][td]MHE Loc[/td][td]MHE Translated[/td][/tr]

[tr][td]

2​

[/td][td]20000000107705400[/td][td]

4​

[/td][td][/td][td]SHIPJP[/td][td]SHIPJP[/td][/tr]

[tr][td]

3​

[/td][td]20000000107708000[/td][td]

4​

[/td][td][/td][td]TWR1EXT2[/td][td]TWR1EXT2[/td][/tr]

[tr][td]

4​

[/td][td]20000000107708000[/td][td]

4​

[/td][td][/td][td]TWR1EXT2[/td][td]TWR1EXT2[/td][/tr]

[tr][td]

5​

[/td][td]20000000107714900[/td][td]

4​

[/td][td]T5000984[/td][td]TWR1EXT2[/td][td]TWR1EXT2[/td][/tr]

[tr][td]

6​

[/td][td]20000000107714900[/td][td]

4​

[/td][td]T5000984[/td][td]TWR1EXT2[/td][td]TWR1EXT2[/td][/tr]

[tr][td]

7​

[/td][td]20000000107714900[/td][td]

4​

[/td][td]T5000984[/td][td]TWR1EXT2[/td][td]TWR1EXT2[/td][/tr]

[tr][td]

8​

[/td][td]20000000107715700[/td][td]

4​

[/td][td]T5000984[/td][td]TWR1EXT2[/td][td]TWR1EXT2[/td][/tr]

[tr][td]

9​

[/td][td]20000000107715700[/td][td]

4​

[/td][td]T5000984[/td][td]TWR1EXT2[/td][td]TWR1EXT2[/td][/tr]

[tr][td]

10​

[/td][td]20000000107715700[/td][td]

4​

[/td][td]T5000984[/td][td]TWR1EXT2[/td][td]TWR1EXT2[/td][/tr]

[tr][td]

11​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

12​

[/td][td]DISTINCT CARTON[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

13​

[/td][td]

4​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

14​

[/td][td]DISTINCT TOTE[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

15​

[/td][td]

1​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

16​

[/td][td]DISTINCT CARTON WHILE TOTE = ""[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

17​

[/td][td]

2​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]

A13, control+shift+enter:

=SUM(IF(FREQUENCY(IF(A2:A10<>"",MATCH("~"&A2:A10,A2:A10&"",0)),ROW(A2:A10)-ROW(A2)+1),1))

A15, control+shift+enter:

=SUM(IF(FREQUENCY(IF(C2:C10<>"",MATCH("~"&C2:C10,C2:C10&"",0)),ROW(C2:C10)-ROW(C2)+1),1))

A17, control+shift+enter:

=SUM(IF(FREQUENCY(IF(A2:A10<>"",IF(C2:C10="",
   MATCH("~"&A2:A10,A2:A10&"",0))),ROW(A2:A10)-ROW(A2)+1),1))

 

[GREENUSER]

Aladin essentially i need to add a date range to this code: of possible.

=COUNTIF('AS CON LIST'!D3:D1400,K5)

Forumula is located in L5:L10 gives total at the moment! i want it to give the total for a set date range,per month!
K5:K10 contains the users list
'AS CON LIST'!D3:D1400 contains the Users and is the value i wish to count
'AS CON LIST'! C3:C1400 contains the date .

I can get the results i want by manually selecting range as the date is chronological however for excersise sake id like to use code featuring date range!

here is a mock table i wish to furfill i hope i have been thorough enough
Thanks in advance your input is much appreciated!


[TABLE="width: 540"]
<TBODY>[TR]
[TD]DATE DRAWN</SPAN>[/TD]
[TD]DRAWN BY</SPAN>[/TD]
[TD]January</SPAN>[/TD]
[TD="align: right"]3</SPAN>[/TD]
[/TR]
[TR]
[TD="align: right"]4/01/2015</SPAN>[/TD]
[TD]G.APUWAI</SPAN>[/TD]
[TD]Inspector</SPAN>[/TD]
[TD]Total drawn</SPAN>[/TD]
[/TR]
[TR]
[TD="align: right"]4/01/2015</SPAN>[/TD]
[TD]B.GOLDSWORTHY</SPAN>[/TD]
[TD]G.APUWAI</SPAN>[/TD]
[TD="align: right"]1</SPAN>[/TD]
[/TR]
[TR]
[TD="align: right"]4/01/2015</SPAN>[/TD]
[TD]G.WOODALL</SPAN>[/TD]
[TD]B.GOLDSWORTHY</SPAN>[/TD]
[TD="align: right"]1</SPAN>[/TD]
[/TR]
[TR]
[TD="align: right"]4/02/2015</SPAN>[/TD]
[TD]C.HOPNER</SPAN>[/TD]
[TD]G.WOODALL</SPAN>[/TD]
[TD="align: right"]1</SPAN>[/TD]
[/TR]
[TR]
[TD="align: right"]4/06/2015</SPAN>[/TD]
[TD]P.JONES</SPAN>[/TD]
[TD]C.HOPNER</SPAN>[/TD]
[TD="align: right"]0</SPAN>[/TD]
[/TR]
[TR]
[TD="align: right"]4/05/2015</SPAN>[/TD]
[TD]R.BULLIMORE</SPAN>[/TD]
[TD]P.JONES</SPAN>[/TD]
[TD="align: right"]0</SPAN>[/TD]
[/TR]
[TR]
[TD="align: right"]4/03/2015</SPAN>[/TD]
[TD]P.PEARSON</SPAN>[/TD]
[TD]R.BULLIMORE</SPAN>[/TD]
[TD="align: right"]0</SPAN>[/TD]
[/TR]
[TR]
[TD="align: right"]5/03/2015</SPAN>[/TD]
[TD]G.APUWAI</SPAN>[/TD]
[TD]P.PEARSON</SPAN>[/TD]
[TD="align: right"]0</SPAN>[/TD]
[/TR]
[TR]
[TD="align: right"]5/06/2015</SPAN>[/TD]
[TD]B.GOLDSWORTHY</SPAN>[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]15/06/2015</SPAN>[/TD]
[TD]G.WOODALL</SPAN>[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</TBODY><COLGROUP><COL><COL><COL><COL></COLGROUP>[/TABLE]

 

[elexdee]

Hi All, First time poster, long time reader.

Again, my problem is really in the same vain as all of the previous ones, but one extra step is causing me grief.

I have a list of Stores where a display was put up, week on week. I need to count all of the “Yes”, where there was no a “Yes” previously recorded, except in the case where a “No” is recorded in between 2 “Yes”, which indicates there is a new display.

Using the below example, Week 4 should have a count of 3, as store A, C & G recorded “No” in the previous week. Store B & H had no visit in week 3, so their display was counted in week 2. Store D still has a display up from week 3.

I originally used a series of countifs, but aside from becoming monotonous, the results were not accurate as it either counted all of the yes’s or omitted any previous regardless of whether a no was recorded in-between visits. As this report builds eventually every store will have had a display put and taken down, then put back up and using countifs would result in new displays being counted as a 0.

Thanks for your help,

Al