LinkBack URL
About LinkBacks
Please would someone be kind enough to help me convert this formula so that people using 2003 can see the data:
=COUNTIFS('Sales'!$A$4:$A$400,"JAN",'Sales'!$V$4:$V$400,1)
Would really appreciate any help at all.
Thank you!!!
Please would someone be kind enough to help me convert this formula so that people using 2003 can see the data:
=COUNTIFS('Sales'!$A$4:$A$400,"JAN",'Sales'!$V$4:$V$400,1)
Would really appreciate any help at all.
Thank you!!!
Try:
=SUMPRODUCT(('Sales'!$A$4:$A$400="JAN")*('Sales'!$V$4:$V$400=1))
Where there is a will there are many ways.
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Just tried it and it has come back with an error #####VALUE
Is the column wide enough to display the result?
yes, I have just checked and I have also expanded it further just incase.
So are you getting ######### or are you getting #VALUE! error?
If the Countifs worked, so should the Sumproduct.
Apologies it is a #VALUE!
So then I think you must have that error in one of the 2 lookup columns.... it's needs to be fixed.
Can you attach your workbook? My guess is that your numbers are probably stored as text, but I'm not really sure if that would affect SUMPRODUCT.
Text entries won't affect this sumproduct formula.
Do you have a comparison statement in all parts of the sumproduct? If so, the formula should work, unless your ranges are different in size. The * operator in Sumproduct will throw an error if one of the component of the sumproducts returns text. A formula like
=SUMPRODUCT(('Sales'!$A$4:$A$400="JAN")*'Sales'!$V$4:$V$400)
will throw an error if column V contains text. In that case, use the -- version, like
=SUMPRODUCT(--('Sales'!$A$4:$A$400="JAN"),'Sales'!$V$4:$V$400)
For a discussion of Sumproduct and its error messages, see here http://spreadsheet-toolbox.com/libra...error-message/
... and no, I don't want your email address, and no, I'm not trying to sell you Excel information that you can get for free if you want.
Last edited by teylyn; 09-22-2010 at 04:54 AM.
properly laid out data would enable a Pivottable to do this
Hope that helps.
RoyUK
--------
For Excel Tips & Solutions, free examples and tutorials why not check out my web site
Free DataBaseForm example
Did you resolve this?
COUNTIFS will ignore errors in the ranges but the suggested SUMPRODUCT and other formulas won't ignore those errors. It would be best to eliminate any errors in the referenced ranges but if you don't this formula might work for you
=SUMPRODUCT(ISNUMBER(MATCH(Sales!$A$4:$A$400,{"JAN"},0))*ISNUMBER(MATCH(Sales!$V$4:$V$400&"",{"1"},0)))
That will ignore errors as COUNTIFS does and also it will count both numeric 1s and text "1"s as COUNTIFS does
Audere est facere
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks