{=SUM(IF(C5:U5=C6:U6,1,0))- COUNTIF(C5:U5,0)}
This is a discussion on Make Formula ignore blank cells within the Excel Questions forums, part of the Question Forums category; ...
Hi all, need help regarding how to make a formula ignore blank cells. I have formulas within a worksheet that looks at the values contained within a row of cells and then compares them with the values in the next row. There are 3 formulas, the first looks to see if the values in the upper row are larger, the second looks for larger values in the lower row and the third looks for matching values. The following shows how i am employing these in my worksheet, basically i'm analysing football results and the formulas give me a breakdown of games won, lost and drawn. The trouble i'm having is that the third formula that looks for matches is comparing blank cells with blank cells and so is distorting my results. How do i get it to ignore blank cells. I've searched through the message boards but after reading through them i'm not sure whether to use Isblank or Isnumber or whatever.
Any help will be greatly appreciated.
An example of my worksheet, the data being analysed is row C5:U5 compared with C6:U6
******** ******************** ************************************************************************>
Microsoft Excel - Footy Tables Rev2a.xls ___Running: xl2000 : OS = Windows XP
(F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout
=
A B C D E F G H I J K L M N O P Q R S T U 5 Arsenal*© H 2 0 1 2 2 2 2 0 5 2 4 2 1 4 3 1 2 2 3 6 A 0 0 0 1 1 1 1 0 0 1 2 1 1 1 2 1 0 1 0 7 Aston*Villa H 0 2 0 1 2 3 0 3 2 3 0 1 0 0 0 2 1 1 3 8 A 2 2 2 1 1 2 0 0 0 1 0 1 2 2 0 1 0 0 2
04 - 05 SCORES *
[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.
This is where the formulas are, all are fine if the data range contains data in every cell. However, when i clear the contents to input new data my formula for counting draws, cell AM5, is counting blanks. Not what i want.
Hope i've made things clear, thanks in advance.
******** ******************** ************************************************************************>
Microsoft Excel - Footy Tables Rev2a.xls ___Running: xl2000 : OS = Windows XP
(F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout
=
AI AJ AK AL AM AN 3 * TOT H*W H*L Draw * 4 * * * * * * 5 * 19 15 0 4 * 6 * * 79% 0% 21% * 7 * 19 9 4 6 *
04 - 05 SCORES *
[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.
What happens when there is no data, Cell AM5 shows 19 draws. Not what i want.
******** ******************** ************************************************************************>
Microsoft Excel - Footy Tables Rev2a.xls ___Running: xl2000 : OS = Windows XP
(F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout
=
AI AJ AK AL AM AN 3 * TOT H*W H*L Draw * 4 * * * * * * 5 * 19 0 0 19 * 6 * * 0% 0% 100% * 7 * 19 9 4 6 *
04 - 05 SCORES *
[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.
{=SUM(IF(C5:U5=C6:U6,1,0))- COUNTIF(C5:U5,0)}
<MARQUEE>...........Never be afraid to try something new. Remember, amateurs built the ark, professionals built the Titanic...............The easiest thing to find is fault, don't be easy !.. --Anonymous--...</marquee>
Hi,
How about a non-array entered function ...
=SUMPRODUCT(--(C5:U5>C6:U6),C5:U5)
(Hi Nimrod!)
Regards,
Zack Barresse
My Book on Excel Tables
My Blog @ ExcelTables.com
(If you would like comments in any code, please say so.)
Hi firefytr
Nice solution
<MARQUEE>...........Never be afraid to try something new. Remember, amateurs built the ark, professionals built the Titanic...............The easiest thing to find is fault, don't be easy !.. --Anonymous--...</marquee>
Hi, thanks for your suggestions, however, they do not give the result i want.
I tried your solution fireftyr but when i filled the range with test data in which there is four draws (so the formula should return 4) i got the return of 38!
I also tried your solution Nimrod but the value return is 2. I checked the test data and there are 2 instances of a draw being 0 0. See below, cells D5&D6 and J5&J6. Is this the reason why the return value is 2 and not 4? Does the formula need to be revised? Is it ignoring instances where the cells are 0 0.
******** ******************** ************************************************************************>
Microsoft Excel - Footy Tables Rev2a.xls ___Running: xl2000 : OS = Windows XP
(F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout
=
B C D E F G H I J K L M N O P Q R S T U 4 * * * * * * * * * * * * * * * * * * * * 5 H 2 0 1 2 2 2 2 0 5 2 4 2 1 4 3 1 2 2 3 6 A 0 0 0 1 1 1 1 0 0 1 2 1 1 1 2 1 0 1 0 7 H 0 2 0 1 2 3 0 3 2 3 0 1 0 0 0 2 1 1 3 8 A 2 2 2 1 1 2 0 0 0 1 0 1 2 2 0 1 0 0 2
04 - 05 SCORES *
[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.
And your result Nimrod
******** ******************** ************************************************************************>
Microsoft Excel - Footy Tables Rev2a.xls ___Running: xl2000 : OS = Windows XP
(F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout
=
AI AJ AK AL AM AN 3 * TOT H*W H*L Draw * 4 * * * * * * 5 * 17 15 0 2 * 6 * * 88% 0% 12% * 7 * 19 9 4 6 * 8 * * 47% 21% 32% *
04 - 05 SCORES *
[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.
Hope you can help
Thanks people.
To just count, something like ...
=SUMPRODUCT(--(C5:U5>C6:U6),--(ISNUMBER(C5:U5)))
And in your above example, it's 15, not four - at least with your example.
Regards,
Zack Barresse
My Book on Excel Tables
My Blog @ ExcelTables.com
(If you would like comments in any code, please say so.)
Still not sure what you are getting at firefytr, tried your suggestion but still not working as i want. Not sure if i have explained myself clearly as to what i'm trying to achieve and whether this is proving confusing.
Also, the return i am looking for is 4 and not the 15 you see reurned. If you look at the example below i have highlighted the cells of data in question. Comparing the value of cells in top row with those in the row underneath on a column by column basis. Thus, there are 19 comparrisons in all.
******** ******************** ************************************************************************>
Microsoft Excel - Footy Tables Rev2a.xls ___Running: xl2000 : OS = Windows XP
(F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout
=
C D E F G H I J K L M N O P Q R S T U 7 0 2 0 1 2 3 0 3 2 3 0 1 0 0 0 2 1 1 3 8 2 2 2 1 1 2 0 0 0 1 0 1 2 2 0 1 0 0 2
04 - 05 SCORES *
[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.
Now, the calculations are undertaken in the following area. As you can see, using the data above returns the results below. Formula in Cell AK5 has calculated 15 instances where the top row cell is greater than the lower row, thus 15 home wins in this case. There are no Home losses, (Cell AL5) and four draws(Cell AM5).
******** ******************** ************************************************************************>
Microsoft Excel - Footy Tables Rev2a.xls ___Running: xl2000 : OS = Windows XP
(F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout
=
AJ AK AL AM 3 TOT H*W H*L Draw 4 * * * * 5 19 15 0 4
04 - 05 SCORES *
[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.
This all works fine when the data range is full but as i've said earlier the formula for calculating draws breaks down when there is no or just a few entries in, counting blank cells as a match. Applying the suggestions from yourself and Nimrod and variations thereof are not quite what i'm looking for, although Nimrods suggestion seemed to work up to a point, i.e. it recognised matches where the cell contents was 1 or above, but not where the contents was 0 & 0.
Sorry if i seem to be going on a bit or being a pest, just that i'm sure there is a simple solution to this frustrating problem. Thanks for your patience.
Cheers
Just thought i'd add that my Htmlmaker does not seem to showing the formulas in individual cells, just seems to be stuck on one for some reason.
Ok, so you're looking to compare the two ranges and count only the pairs that EQUAL each other, but not zero? If that is the case, try ...
=SUMPRODUCT(--(C5:U5=C6:U6),--(C5:U5<>0))
Regards,
Zack Barresse
My Book on Excel Tables
My Blog @ ExcelTables.com
(If you would like comments in any code, please say so.)
Nearly there, looking to compare the two ranges and count only the pairs that EQUAL each other, but INCLUDE zero (nil nil or zero zero is a valid score) but NOT empty cells. At the moment the ranges are full of data, in real usage they start off blank and will have entries added week by week, so blank cells have to be accounted for.
Thanks
Like this thread? Share it with others