I am using a sumproduct array that is setup to reference a table filled with sales data. I am trying to get cells Chart D3:M3 to populate with #N/A so the chart formats better. From what I have read, using an if statement with NA() does not work with a sumproduct. If you look in my example file, Chart B6:D6, I have included the formula that I thought would work. D6 is formatted right, but B6:C6 are not adding up the numbers correctly.
There's nothing wrong with the approach you're using with the formula. The problem is coming from your named table. It is changing/rotating the column titles as you use your autofill. Try naming each column instead of just the table.
Cheers,
Would you like to say thanks? Please click the: "Add Reputation" button, on the grey bar below the post.
I'm not sure how changing the column names will make the formula work correctly. If you look in B6, the value = 0 when then the correct value should be 43436 as found in B3.
Can you please provide more information on how to update the table to allow the formula to work correctly.
Here's the formula you're using in B6:
Note the error in red; "D" should be "B". The error is repeated in cell C6 where "D" should be "C".
Please Login or Register to view this content.
Cheers,
Last edited by ConneXionLost; 10-07-2010 at 02:37 AM.
It looks like my formula was wrong as you said. After referencing the correct fields, the formula worked as expected.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks