| Home |
| Search |
| Today's Posts |
|
#1
|
|||
|
|||
IF Statement Question 0 values & blank cells
Here is my formula:
IF(SUMPRODUCT(($A$3:$A$5=$A$8)*($B$2:$E$2=$B$7)*($ B$3:$E$5))="","", SUMPRODUCT(($A$3:$A$5=$A$8)*($B$2:$E$2=$B$7)*($B$3 :$E$5))) I get a 0 value for a blank cell.. I would like nothing returned for a blank cell. If I use =0, "", I will not receive a value for cells that have a 0 in them. Help! Thanks, Josh |
|
#2
|
|||
|
|||
|
One possible way
=IF(COUNTBLANK(INDEX(A3:E6,0,MATCH(B7,A2:E2,0)))=4 ,"",SUMPRODUCT(($A$3:$A$5=$A$8)*($B$2:$E$2=$B$7)*( $B$3:$E$5))) -- Regards, Peo Sjoblom (No private emails please) "joshmd9909" wrote in message ... Here is my formula: IF(SUMPRODUCT(($A$3:$A$5=$A$8)*($B$2:$E$2=$B$7)*($ B$3:$E$5))="","", SUMPRODUCT(($A$3:$A$5=$A$8)*($B$2:$E$2=$B$7)*($B$3 :$E$5))) I get a 0 value for a blank cell.. I would like nothing returned for a blank cell. If I use =0, "", I will not receive a value for cells that have a 0 in them. Help! Thanks, Josh |
|
#3
|
|||
|
|||
|
I tried the formula below and I am receiving a 0 value when the data cell is
blank. "Peo Sjoblom" wrote: One possible way =IF(COUNTBLANK(INDEX(A3:E6,0,MATCH(B7,A2:E2,0)))=4 ,"",SUMPRODUCT(($A$3:$A$5=$A$8)*($B$2:$E$2=$B$7)*( $B$3:$E$5))) -- Regards, Peo Sjoblom (No private emails please) "joshmd9909" wrote in message ... Here is my formula: IF(SUMPRODUCT(($A$3:$A$5=$A$8)*($B$2:$E$2=$B$7)*($ B$3:$E$5))="","", SUMPRODUCT(($A$3:$A$5=$A$8)*($B$2:$E$2=$B$7)*($B$3 :$E$5))) I get a 0 value for a blank cell.. I would like nothing returned for a blank cell. If I use =0, "", I will not receive a value for cells that have a 0 in them. Help! Thanks, Josh |
|
#4
|
|||
|
|||
|
It worked for me when I tested a small sample, however I don't know what
your data cell is, maybe if you could describe how the layout is and what's in the cells? -- Regards, Peo Sjoblom (No private emails please) "joshmd9909" wrote in message ... I tried the formula below and I am receiving a 0 value when the data cell is blank. "Peo Sjoblom" wrote: One possible way =IF(COUNTBLANK(INDEX(A3:E6,0,MATCH(B7,A2:E2,0)))=4 ,"",SUMPRODUCT(($A$3:$A$5=$A$8)*($B$2:$E$2=$B$7)*( $B$3:$E$5))) -- Regards, Peo Sjoblom (No private emails please) "joshmd9909" wrote in message ... Here is my formula: IF(SUMPRODUCT(($A$3:$A$5=$A$8)*($B$2:$E$2=$B$7)*($ B$3:$E$5))="","", SUMPRODUCT(($A$3:$A$5=$A$8)*($B$2:$E$2=$B$7)*($B$3 :$E$5))) I get a 0 value for a blank cell.. I would like nothing returned for a blank cell. If I use =0, "", I will not receive a value for cells that have a 0 in them. Help! Thanks, Josh |
|
#5
|
|||
|
|||
|
Here is how my data is set up.
Column A is a set of metric ID's. Row 2 has the dates in them. B3-E5 in this example is where the data is. Example 10/01/01 10/08/05 1354 -1.5 0 1469 25 "blank cell" 1568 33,000 40,000 Then on a different spreadsheet I have the metric ID #'s listed in column A & a single date that is in cell B7. I'm comparing metric ID's & the date and then pulling the data where that criteria is matched. Sometimes I will have a blank cell though, and I need a blank returned, not a 0!! However as you can see some of the data will have a 0 value & I also need that returned. Thanks for your help in advance! IF(SUMPRODUCT(($A$3:$A$5=$A$8)*($B$2:$E$2=$B$7)*($ B$3:$E$5))="","", SUMPRODUCT(($A$3:$A$5=$A$8)*($B$2:$E$2=$B$7)*($B$3 :$E$5))) "Peo Sjoblom" wrote: It worked for me when I tested a small sample, however I don't know what your data cell is, maybe if you could describe how the layout is and what's in the cells? -- Regards, Peo Sjoblom (No private emails please) "joshmd9909" wrote in message ... I tried the formula below and I am receiving a 0 value when the data cell is blank. "Peo Sjoblom" wrote: One possible way =IF(COUNTBLANK(INDEX(A3:E6,0,MATCH(B7,A2:E2,0)))=4 ,"",SUMPRODUCT(($A$3:$A$5=$A$8)*($B$2:$E$2=$B$7)*( $B$3:$E$5))) -- Regards, Peo Sjoblom (No private emails please) "joshmd9909" wrote in message ... Here is my formula: IF(SUMPRODUCT(($A$3:$A$5=$A$8)*($B$2:$E$2=$B$7)*($ B$3:$E$5))="","", SUMPRODUCT(($A$3:$A$5=$A$8)*($B$2:$E$2=$B$7)*($B$3 :$E$5))) I get a 0 value for a blank cell.. I would like nothing returned for a blank cell. If I use =0, "", I will not receive a value for cells that have a 0 in them. Help! Thanks, Josh |
|
#6
|
|||
|
|||
|
Just tinkering around ..
Assuming this is in B3:E5 10/01/01 10/08/05 1354 -1.5 0 1469 25 "blank cell" 1568 33,000 40,000 and if we have: in A8: 1469 in B7: 10/08/05 Putting in say, B8: =IF(ISBLANK(INDIRECT( ADDRESS(MATCH(A8,A:A,0),MATCH(B7,2:2,0)))),"", INDIRECT(ADDRESS(MATCH(A8,A:A,0),MATCH(B7,2:2,0))) ) seems to return the desired result, i.e. blank Changing the input in A8 to say: 1354 would return in B8: 0, and so on .. -- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 -- |
|
#7
|
|||
|
|||
|
Correction to description of set-up in earlier response, sorry ..
Line: Assuming this is in B3:E5 should read as: Assuming this is in A2:C5 -- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 -- |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Count Intervals of Filtered TEXT values in Column and Return Count across a Row | Excel Worksheet Functions | |||
| Condensing a list/range with blank cells to a new list/range without blanks | Excel Worksheet Functions | |||
| Counting blank and filled cells within a range. | Excel Discussion (Misc queries) | |||
| HELP: Last row of the column containing blank cells between non-blank cells | Excel Worksheet Functions | |||
| In Exel 2000, stop the blank cells (with formulas) from printing. | Excel Discussion (Misc queries) | |||
Linear Mode
