SUMPRODUCT with today Int

[magpie2000k]

Guys I have a spread sheet where in column AF I have dates.
Not all cells have dates as the column is the date the lead was passed to sales and not all are passed to sales. As such some cells in that column are blank.

However the ones that are not blank have a date in the dd/mm/yyyy hh:mm:ss format

I want to count them if they are today irrespective of time.

=SUMPRODUCT(--(INT(Leads.csv!$AF1:$AF10000)=TODAY()))

What have I done wong as returns #VALUE !

Yes I entered it as an array the formulat also needs to include where column L = Internet/Google Phone

Thanks in advance

 

[magpie2000k]

Re: Help with SUMPRODUCT with today Int

sorry worked it out

But I now need to add to the following

=COUNTIFS(Leads.csv!$AF:$AF,">="&TODAY(),Leads.csv!$AF:$AF,"<"&TODAY()+1,Leads.csv!$AH:$AH,B3,Leads.csv!$AB:$AB,"<>existing",Leads.csv!$M:$M,"<>*Void*",Leads.csv!$L:$L,"<>Cold call")

Where the date in column G does not = today (the format in column G is still dd/mm/yyy hh:mm:ss)

 

[steve the fish]

Re: Help with SUMPRODUCT with today Int

You can write a load of different formulas or use a cell references. Put 9:00 in A1 and 10:00 in A2 then use something like ">="&TODAY()+A1 for one and "<"&TODAY()+A2 to give an example.

 

[magpie2000k]

Re: Help with SUMPRODUCT with today Int

You can write a load of different formulas or use a cell references. Put 9:00 in A1 and 10:00 in A2 then use something like ">="&TODAY()+A1 for one and "<"&TODAY()+A2 to give an example.

Hi Sorry yeah I get that now. But I want to pull something through when the day was not today ie yestedray or 5 days agi. so simply anything in column where the date and time was before today.

Hope that makes sense

 

[magpie2000k]

Re: Help with SUMPRODUCT with today Int

I am nearly there

All formulas are working but I am getting a result I dont think is correct

In column M I have the current status

The statuses are many but include

Possible Void
Voided
Voided admin
Voided existing
Voided finance

I wrote the formula like this

Leads.csv!$M:$M,"<>*Void*"

Or the whole formula if it helps

=COUNTIFS(Leads.csv!$G:$G,">="&TODAY(),Leads.csv!$G:$G,"<"&TODAY()+1,Leads.csv!$AH:$AH,$B3,Leads.csv!$AF:$AF,"<>"&"*",Leads.csv!$AB:$AB,"<>existing",Leads.csv!$M:$M,"<>*Void*",Leads.csv!$L:$L,"<>Cold call",Leads.csv!$K:$K,"<>ADMIN")

The answer should be 4 but it returns ten

There are 6 possible voided for the querey so thinking that the wild cards I am using arent working