Cant get sumproduct to work: any idea why?

[sam singha]

hi Friends,

I am attaching a file with data i am working upon. I have used sumproduct to derive a value based on multiple criteria but it keeps returning 0. I have used this data before and used almost identical formula. Not sure why its acting up this time. Can anyone suggest what i am not seeing?

 

[Chihiro]

That's because year column in the table uses following formula.
=TEXT([@Date],"yyyy")

This returns value as text and will not match numeric value of 2016 (i.e. always returns false or 0 for the check).

Change formula for the column as following.
=YEAR([@Date])

Edit: Or alternately use the date column and change condition check to something like... (YEAR(Table1[Date])=R7).
Not sure what result you are looking for exactly as your current formula is really countifs formula (counting number of times all criteria are met).

 

[sam singha]

Thanks Chihiro! you are right. I actually figured it out also. Countifs will do just fine also but there is another result which needs the sum of the 'amounts' for particular months and other criteria. so i chose sumproduct for both the queries. I am putting this together for a sales dashboard. will share with you once its completed. I have some more challenges that needs to get worked out in its making. I hope i can expect your advise in that .

thanks again.

That's because year column in the table uses following formula.
=TEXT([@Date],"yyyy")

This returns value as text and will not match numeric value of 2016 (i.e. always returns false or 0 for the check).

Change formula for the column as following.
=YEAR([@Date])

Edit: Or alternately use the date column and change condition check to something like... (YEAR(Table1[Date])=R7).
Not sure what result you are looking for exactly as your current formula is really countifs formula (counting number of times all criteria are met).

 

[sam singha]

Hi Chihirio,

To find out how many clients we have for a particular state i need to run this query. i have attached a sheet with sample addresses and states names list. i want to take the count of clients against each state name. i was trying combo of search and countifs but did nto work. Any advise?

Thanks Chihiro! you are right. I actually figured it out also. Countifs will do just fine also but there is another result which needs the sum of the 'amounts' for particular months and other criteria. so i chose sumproduct for both the queries. I am putting this together for a sales dashboard. will share with you once its completed. I have some more challenges that needs to get worked out in its making. I hope i can expect your advise in that .

thanks again.

 

[Chihiro]

Your lookup list seems incomplete.

But if you have complete list of cities/states... you can do something like.

In C2:
=INDEX(Sheet1!$A$2:$A$30,MATCH(FALSE,ISERROR(SEARCH(Sheet1!$A$2:$A$30,A2)),0)

Confirmed as array (CSE) & copy down. This will list matching States. However, there are some spelling errors in data (See A7 in contact list).

In D2:
=INDEX(Sheet1!$A$2:$A$30,MATCH(FALSE,ISERROR(SEARCH(Sheet1!$B$2:$B$30,A2)),0))

Confirmed as array (CSE) & copy down. Same method as above but matching Capital City and returning States.

In E2:
=IF(ISERROR(C2),D2,C2)

This is to consolidate the previous 2 column. Then you can do count operation on column E.

See attached.

 

[sam singha]

That is fantastic! i am in still in a process to understand the formulae but it works perfectly after some spelling correction(and instructions to the admin to to keep data consistent while typing in customer info into CRM).

Thank again

Your lookup list seems incomplete.

But if you have complete list of cities/states... you can do something like.

In C2:
=INDEX(Sheet1!$A$2:$A$30,MATCH(FALSE,ISERROR(SEARCH(Sheet1!$A$2:$A$30,A2)),0)

Confirmed as array (CSE) & copy down. This will list matching States. However, there are some spelling errors in data (See A7 in contact list).

In D2:
=INDEX(Sheet1!$A$2:$A$30,MATCH(FALSE,ISERROR(SEARCH(Sheet1!$B$2:$B$30,A2)),0))

Confirmed as array (CSE) & copy down. Same method as above but matching Capital City and returning States.

In E2:
=IF(ISERROR(C2),D2,C2)

This is to consolidate the previous 2 column. Then you can do count operation on column E.

See attached.