I tried but failed (Countif based questions)

[mtahboub]

Hello people,

I really hate to post anything here before trying. Both for self-satisfaction and to not clog unnecessarily.

Well, I tried but couldn't get through this one so I was wondering if any one can help me.

I have used this formula to count all records that are fall between 1st and 30th of April 2013 and are listed as "X351 Jaguar XJ" in column CC on "MYNM Jaguar" tab.

=COUNTIFS('MYNM Jaguar'!H1:H38,">="&$B$3,'MYNM Jaguar'!H1:H38,"<="&$B$4,'MYNM Jaguar'!BR1:BR38,"="&Sheet1!$A$4)

No records were coming back unfortunately though I know this should not be the case judging by the list.

Can anyone help? File attached for reference

[ben_hensel]

H1:H38 is the "factory order number" column.
BR1:BR38 is the "export licence number" column.

B7 =COUNTIFS('MYNM Jaguar'!$B$1:$B$38,">="&$B3,'MYNM Jaguar'!$B$1:$B$38,"<="&$B4,'MYNM Jaguar'!$CC$1:$CC$38,"="&Sheet1!$A$4)

That do it for you?

Note this is going to return zero even if it's correct with the test data set because you're checking for dates in 2013 when all your test dates are in 2012, it looks like. I got "29" when I expanded the test range to stretch from 2010 to 2013.

[mtahboub]

HiBen,

Thanks for your answer.

I am looking for records that satisfy the following criteria:

1. Date between 4/1/2013 and 4/30/2013 (in column S in "MYNM Jaguar" tab)
2. = "X351 Jaguar XJ" (in column CC in "MYNM Jaguar" tab)

@ben_hensel: the solution you provided did not really work out. And there are columns with April 2013 date in "MYNM Jaguar" tab - column S.

Thanks again

[ben_hensel]

So all you need to do is refer to S1:S38 rather than B1:B38 or H1:H38 then, right?

Does that mean this is solved?

[mtahboub]

So all you need to do is refer to S1:S38 rather than B1:B38 or H1:H38 then, right?

Does that mean this is solved?

Hello Ben,

Yes, it works fine now.

Once I ran this formulate for the larger data ranger, I came to the conclusion that I need to be substitute one of the criteria from Column CC to Column CG

=COUNTIFS('MYNM Jaguar'!$S$1:$S$2000,">="&B$3,'MYNM Jaguar'!$S$1:$S$2000,"<="&B$4,'MYNM Jaguar'!$CC$1:$CC$2000,"="&Sheet1!$A$4)

The trick is, I will need to extract the first 2 characters from the fields in Column CG. I tried LEFT (,) but that did not work. Can you give me this last push?

Thanks

[Imran Laldin]

Dear, Try This.

=COUNTIFS(MYNMJaguar!$B$2:$B$38,">="&Sheet1!B3,MYNMJaguar!$B$2:$B$38,"<="&Sheet1!B4,MYNMJaguar!$CC$2:$CC$38,Sheet1!$A$4)

[ben_hensel]

COUNTIFS can't handle arrayed inputs, which is what's going to happen with your LEFT. So what you want is directly impossible, but there are still ways to handle it.

There are two ways I would do this:

1) Add a helper column, for example insert a new column next to CG that will be your new CH, and fill it with:
CH2 = LEFT(CG2, 2)
pull down

And then use that column CH as the condition for the COUNTIF instead of column CG.



2) Use an array-type formula instead of COUNTIFS.

For now, I'm putting this additional text in:
sheet1!A3 = "XJ"
that I will be using as a comparitor instead of the full name in sheet1!A4.

2.1) Using SUMPRODUCT()

=SUMPRODUCT(--(LEFT('MYNM Jaguar'!$CG$2:$CG$2000,2)=$A$3), --('MYNM Jaguar'!$S$2:$S$2000>=B$3), --('MYNM Jaguar'!$S$2:$S$2000<=B$4))

2.2) Using {SUM(IF(array))}

{=SUM(IF(LEFT('MYNM Jaguar'!$CG$2:$CG$2000,2)=$A$3,1,0)*IF('MYNM Jaguar'!$S$2:$S$2000>=B$3,1,0)*IF('MYNM Jaguar'!$S$2:$S$2000<=B$4,1,0))}

Note that, as a array formula, this has to be executed with CTRL+SHIFT+ENTER instead of just hitting ENTER.




See attached for a demonstration of how these would work.