Countifs to count a row equals another row

[ncurran217]

I am looking to have a table with a column of dates and another column to show how many from my list of data are not equal of my estimations to actual numbers. I have attached a sample data in the excel sheet. On the Info tab, I have 2 dates and in column B I want to show out of those two dates, how many from the Data tab where Data.Column B does not equal Data.Column C. Hopefully what I am asking is clear, let me know if it is not. Thanks in advance for the help!

[Pete_UK]

You can use this formula in B2:

Please Login or Register  to view this content.

and copy into B3, which will give you 0 and 3, as expected. For your real data, you may need to extend the ranges from $31 (it's not a good idea to use full-column references with SUMPRODUCT).

Hope this helps.

Pete

[ncurran217]

Works good, never used the SUMPRODUCT function before! What are the limitations with this function as when I created into my bigger sheet it says ran out of memory. I ended up limiting the column size like you did with the A$2:A$31, instead of the whole column?

[Pete_UK]

SP will check every cell in the range, even if some are empty, so with a long range it can be very memory-hungry as well as taking a long time to calculate. COUNTIF/SUMIF and others, however, are intelligent enough to just make use of the used-range, so it doesn't really matter if you use full-column references with those. BUT, you can't compare two ranges cell-by-cell with those.

Hope this helps.

Pete

[ncurran217]

Yes it does, thank you very much!