If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
SUMPRODUCT more help
I have a column of dates and a corresponding column of values that are
actually functions converting a foreign currency amount into USD. I want to create a month-to-date total in a single cell by using SUMPRODUCT. What I am using is: =SUMPRODUCT(--(YEAR($A$15:$A$1000)=YEAR($C$7)),--(MONTH($A$15:$A$1000)=MONTH($C$7)),$B$15:$B$1000) But I am getting the #VALUE! error message. Some of the cells in the dates array are blank, and there may be a cell in the values array that has an error because the values are functions themselves. How can I fix this? If I can assure that none of the cells in the value array have errors will that make it easier? |
#2
|
|||
|
|||
If any of the cells have VALUE errors, you'll get that message. Blank cells
are OK. If the cell contains some text, you'll be fine with the way your formula is written. You can count error messages in a range with; =SUMPRODUCT(--ISERROR(A1:A100)) "benb" wrote in message ... I have a column of dates and a corresponding column of values that are actually functions converting a foreign currency amount into USD. I want to create a month-to-date total in a single cell by using SUMPRODUCT. What I am using is: =SUMPRODUCT(--(YEAR($A$15:$A$1000)=YEAR($C$7)),--(MONTH($A$15:$A$1000)=MONTH ($C$7)),$B$15:$B$1000) But I am getting the #VALUE! error message. Some of the cells in the dates array are blank, and there may be a cell in the values array that has an error because the values are functions themselves. How can I fix this? If I can assure that none of the cells in the value array have errors will that make it easier? |
#3
|
|||
|
|||
Personally, I would fix the data and ensure there are no errors there.
-- HTH ------- Bob Phillips "Dave R." wrote in message ... If any of the cells have VALUE errors, you'll get that message. Blank cells are OK. If the cell contains some text, you'll be fine with the way your formula is written. You can count error messages in a range with; =SUMPRODUCT(--ISERROR(A1:A100)) "benb" wrote in message ... I have a column of dates and a corresponding column of values that are actually functions converting a foreign currency amount into USD. I want to create a month-to-date total in a single cell by using SUMPRODUCT. What I am using is: =SUMPRODUCT(--(YEAR($A$15:$A$1000)=YEAR($C$7)),--(MONTH($A$15:$A$1000)=MONTH ($C$7)),$B$15:$B$1000) But I am getting the #VALUE! error message. Some of the cells in the dates array are blank, and there may be a cell in the values array that has an error because the values are functions themselves. How can I fix this? If I can assure that none of the cells in the value array have errors will that make it easier? |
#4
|
|||
|
|||
There are no errors, but I am still coming up with the #VALUE! error message
using SUMPRODUCT(). I've tried a few things, changing the formula and using a dummy dataset, but I couldn't figure out why I was getting the error. Fortunately I found a SUMIF variation that worked instead, but I'm still curious what's causing the error in SUMPRODUCT. "Bob Phillips" wrote: Personally, I would fix the data and ensure there are no errors there. -- HTH ------- Bob Phillips "Dave R." wrote in message ... If any of the cells have VALUE errors, you'll get that message. Blank cells are OK. If the cell contains some text, you'll be fine with the way your formula is written. You can count error messages in a range with; =SUMPRODUCT(--ISERROR(A1:A100)) "benb" wrote in message ... I have a column of dates and a corresponding column of values that are actually functions converting a foreign currency amount into USD. I want to create a month-to-date total in a single cell by using SUMPRODUCT. What I am using is: =SUMPRODUCT(--(YEAR($A$15:$A$1000)=YEAR($C$7)),--(MONTH($A$15:$A$1000)=MONTH ($C$7)),$B$15:$B$1000) But I am getting the #VALUE! error message. Some of the cells in the dates array are blank, and there may be a cell in the values array that has an error because the values are functions themselves. How can I fix this? If I can assure that none of the cells in the value array have errors will that make it easier? |
#5
|
|||
|
|||
Show us the SUMIF variation that works, and we might be able to figure it.
-- HTH Bob Phillips "benb" wrote in message ... There are no errors, but I am still coming up with the #VALUE! error message using SUMPRODUCT(). I've tried a few things, changing the formula and using a dummy dataset, but I couldn't figure out why I was getting the error. Fortunately I found a SUMIF variation that worked instead, but I'm still curious what's causing the error in SUMPRODUCT. "Bob Phillips" wrote: Personally, I would fix the data and ensure there are no errors there. -- HTH ------- Bob Phillips "Dave R." wrote in message ... If any of the cells have VALUE errors, you'll get that message. Blank cells are OK. If the cell contains some text, you'll be fine with the way your formula is written. You can count error messages in a range with; =SUMPRODUCT(--ISERROR(A1:A100)) "benb" wrote in message ... I have a column of dates and a corresponding column of values that are actually functions converting a foreign currency amount into USD. I want to create a month-to-date total in a single cell by using SUMPRODUCT. What I am using is: =SUMPRODUCT(--(YEAR($A$15:$A$1000)=YEAR($C$7)),--(MONTH($A$15:$A$1000)=MONTH ($C$7)),$B$15:$B$1000) But I am getting the #VALUE! error message. Some of the cells in the dates array are blank, and there may be a cell in the values array that has an error because the values are functions themselves. How can I fix this? If I can assure that none of the cells in the value array have errors will that make it easier? |
#6
|
|||
|
|||
You'll get that error if your arrays contain a non-numeric header. If row 15
contains the name of your field, for example. "Bob Phillips" wrote: Show us the SUMIF variation that works, and we might be able to figure it. -- HTH Bob Phillips "benb" wrote in message ... There are no errors, but I am still coming up with the #VALUE! error message using SUMPRODUCT(). I've tried a few things, changing the formula and using a dummy dataset, but I couldn't figure out why I was getting the error. Fortunately I found a SUMIF variation that worked instead, but I'm still curious what's causing the error in SUMPRODUCT. "Bob Phillips" wrote: Personally, I would fix the data and ensure there are no errors there. -- HTH ------- Bob Phillips "Dave R." wrote in message ... If any of the cells have VALUE errors, you'll get that message. Blank cells are OK. If the cell contains some text, you'll be fine with the way your formula is written. You can count error messages in a range with; =SUMPRODUCT(--ISERROR(A1:A100)) "benb" wrote in message ... I have a column of dates and a corresponding column of values that are actually functions converting a foreign currency amount into USD. I want to create a month-to-date total in a single cell by using SUMPRODUCT. What I am using is: =SUMPRODUCT(--(YEAR($A$15:$A$1000)=YEAR($C$7)),--(MONTH($A$15:$A$1000)=MONTH ($C$7)),$B$15:$B$1000) But I am getting the #VALUE! error message. Some of the cells in the dates array are blank, and there may be a cell in the values array that has an error because the values are functions themselves. How can I fix this? If I can assure that none of the cells in the value array have errors will that make it easier? |
#7
|
|||
|
|||
Someone suggested using =SUMIF(A:A,""&(TODAY()-DAY(TODAY())),B:B)
I adapted that into =SUMPRODUCT(--($A$15:$A$1000($Z$7-DAY($Z$7))),--($A$15:$A$1000=$Z$7),Z$15:Z$1000) This version worked. Something in the MONTH() and YEAR() was giving me the #VALUE! error. I thought it might be the headers, but when I excluded those from the array I still got the error. The experience has been worthwhile though because it's forced me to learn much more about SUMPRODUCT. "Bob Phillips" wrote: Show us the SUMIF variation that works, and we might be able to figure it. -- HTH Bob Phillips "benb" wrote in message ... There are no errors, but I am still coming up with the #VALUE! error message using SUMPRODUCT(). I've tried a few things, changing the formula and using a dummy dataset, but I couldn't figure out why I was getting the error. Fortunately I found a SUMIF variation that worked instead, but I'm still curious what's causing the error in SUMPRODUCT. "Bob Phillips" wrote: Personally, I would fix the data and ensure there are no errors there. -- HTH ------- Bob Phillips "Dave R." wrote in message ... If any of the cells have VALUE errors, you'll get that message. Blank cells are OK. If the cell contains some text, you'll be fine with the way your formula is written. You can count error messages in a range with; =SUMPRODUCT(--ISERROR(A1:A100)) "benb" wrote in message ... I have a column of dates and a corresponding column of values that are actually functions converting a foreign currency amount into USD. I want to create a month-to-date total in a single cell by using SUMPRODUCT. What I am using is: =SUMPRODUCT(--(YEAR($A$15:$A$1000)=YEAR($C$7)),--(MONTH($A$15:$A$1000)=MONTH ($C$7)),$B$15:$B$1000) But I am getting the #VALUE! error message. Some of the cells in the dates array are blank, and there may be a cell in the values array that has an error because the values are functions themselves. How can I fix this? If I can assure that none of the cells in the value array have errors will that make it easier? |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Using a named range in a Sumproduct comparison | Hari | General Discussion | 7 | August 23rd, 2004 06:48 PM |
SUMIF vs SUM(IF(..)) vs SUMPRODUCT | Harlan Grove | Worksheet Functions | 7 | July 1st, 2004 10:22 PM |
SUMIF vs SUM(IF(..)) vs SUMPRODUCT | Harlan Grove | General Discussion | 7 | July 1st, 2004 10:22 PM |
Sumproduct and zero's..help please | www.ttdown.com | Worksheet Functions | 6 | March 21st, 2004 01:19 PM |
If & SumProduct | Otto Moehrbach | Worksheet Functions | 6 | November 14th, 2003 11:43 AM |