sumproduct/VLOOKUP with countif?

[smandy]

I am trying to do a count by text criteria for a particular date. The formula that that is currently in the spreadsheet is time consuming and user unfriendly and needs to have the range adjusted daily:

=COUNTIF('Data-All-2012-13'!$G$2886:$G$2906,"NTH")



[TABLE="width: 500"]
<TBODY>[TR]
[TD]Date[/TD]
[TD]NTH[/TD]
[TD]STH[/TD]
[TD]WEST[/TD]
[/TR]
[TR]
[TD]20/6/2013[/TD]
[TD]Formula here totalling # of NTH for 20/6/2013[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]21/6/2013[/TD]
[TD]Formula here totalling # of NTH for 21/6/2013[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</TBODY>[/TABLE]


'Data-All-2012-13' is the sheet being referenced:
[TABLE="width: 500"]
<TBODY>[TR]
[TD]Date[/TD]
[TD]Group[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]20/6/2013[/TD]
[TD]NTH[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]20/6/2013[/TD]
[TD]STH[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]20/6/2013[/TD]
[TD]NTH[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</TBODY>[/TABLE]


Neither of the 2 formulas below have worked:

=SUMPRODUCT(--('Data-All-2012-13'!$G$4:$G$3000=DATE(21,6,2013)),--('Data-All-2012-13'!$G$4:$G$3000=NTH"))

{=SUM(('Data-All-2012-13'!$D$4:$D$4002=$A312)*('Data-All-2012-13'!$G$4:$G$4002=B$4))}

any help would me muchly appreciated!
Thanks

 

[Firefly2012]

Hi

Are your dates actual discrete date values - they are not, for example, date & time (so non-whole numbers) or they are not text values instead of dates?

Regarding your SUMPRODUCT, DATE takes its arguments as Y,M,D so you look like you have them round the wrong way.

 

[smandy]

Thanks for your reply, the dates are Australian, so 20/6/2013 is the correct way. With the SUMPRODUCT I experimented and it didnt work either way. Would it be in the date formatting? ie 2-Jun-13

 

[Firefly2012]

Did you apply the correct sequence of arguments to DATE() as I intimated? That would result in:

=SUMPRODUCT(--('Data-All-2012-13'!$G$4:$G$3000=DATE(2013,6,21)),--('Data-All-2012-13'!$G$4:$G$3000=NTH"))

 

[smandy]

Yes I applied that with no success?

 

[pgc01]

Thanks for your reply, the dates are Australian, so 20/6/2013 is the correct way. With the SUMPRODUCT I experimented and it didnt work either way. Would it be in the date formatting? ie 2-Jun-13

Hi smandy

Excel doesn't care about your formatting for the calculation. Formatting is for you, as a human, to make it pretty or easy to read.

So the question is: is it an excel date value (a number) or is it a text value?

You can test it using for ex.

=ISNUMBER(A1) where A1 would be the cell with the date.

This will help to understand the problem

 

[smandy]

Thanks so much, I got the formula :

=SUMPRODUCT(--('Data-All-2012-13'!$D$4:$D$3000=DATE(2013,6,21)),--('Data-All-2012-13'!$G$4:$G$3000=NTH"))

to work, it was throwing back #NA due to the VLOOKUP in the range being accessed, I solved that with the iserror formula and viola!!

Thanks heaps for the input, much appreciated