• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Sumproduct formula

S

Sreehari

Member
I am currently working on a dashboard, I have to count the columns <>0 in sheet 2 for a particular year


I tried using the formula in my dashboard sheet "=SUMPRODUCT ((sheet2!A5:A42=B3)*(sheet2!J5:J42<>0))" which is giving me a wrong result.


Here Sheet2!A5:A42 contains years from 2012 till 2017 , B3 contains years and column J5:J42 is my criteria column for counting <>0 values.



Please help me with a correct equation
 
=COUNTIFS(Sheet2!A5:A42, B3, Sheet2!J5:J42, "<>"&0)

I'm also confused because you say "columns <>0"
But the formulas you have will count Rows ?

If Bosco's or my forumlas don't help you I'd suggest posting a sample file
 
Hi

This is the actual formula I used.

=SUMPRODUCT((National_disputes_Outpay_Aging!$A$2:$A$43 = B4) * (National_disputes_Outpay_Aging!$J$2:$J$43 > 0))

My expected result is 0 for 2012 in #dispute invoice & for 2013 the value should be 2 ,but instead of that I am getting below attached result.

dashboard

upload_2017-12-1_16-57-43.png
Data sheet"National_disputes_Outpay_Aging"
upload_2017-12-1_16-58-49.png
 
Dear Narayan,

I uploaded the full working sheets as required by you.

Sreehari
 

Attachments

  • Book5.xlsx
    27.4 KB · Views: 1
Hi ,

What is your definition of disputed invoices ?

Is it :

National_disputes_Outpay_Aging!$J$6:$J$43 > 0

or is it :

National_disputes_Outpay_Aging!$J$6:$J$43 = 0

Narayan
 
Sreehari said:
it is

National_disputes_Outpay_Aging!$J$6:$J$43 > 0

or

National_disputes_Outpay_Aging!$J$6:$J$43 <> 0
Click to expand...
Hi ,

Then how you justify this statement from your earlier post ?
My expected result is 0 for 2012 in #dispute invoice & for 2013 the value should be 2 ,but instead of that I am getting below attached result.
Click to expand...
Narayan
 
Hi ,

Oh my god , your are correct , I overlooked and confused. sorry , thanks for highlighting , but for 2014 I should get 11 as output but instead I am getting 0 why?.

Please help.

Also please help me with a formula for Total invoiced amount column in summary dashboard .The expected result in this column is sum total of column Amount( excluding GST) in sheet National_disputes_Outpay_Aging.
 
Hi Narayan,

I got the answer to all my quires , Thanks a lot for your time and support.

for 2014 . I wrongly entered the Year as 2104 instead of 2014 :):(. again my mistake.
 
You must log in or register to reply here.
Back
Top