| Home |
| Search |
| Today's Posts |
|
#1
|
|||
|
|||
sumproduct - deduct one from another.
I have he following code. i want to count all the 12's in columnA range
2-30000 on a month picked in cell H5 and a year J5 and then deduct those records where priority = 6 and month =h5 and year =j5. Just cannot get the thing to work. i am sure it is the brackets. =SUMPRODUCT(--('Imported Data'!$A$2:$A$30000="12*"),--(MONTH('Imported Data'!$I$2:$I$30000)=H5),--(YEAR('Imported Data'!$I$2:$I$30000)=J5)-SUMPRODUCT(--('Imported Data'!$C$2:$C$30000=6),--(MONTH('Imported Data'!$I$2:$I$30000)=H5),--(YEAR('Imported Data'!$I$2:$I$30000)=J5))) thanks in advance -- Regards vipa |
|
#2
|
|||
|
|||
|
Hi
You do have a problem with your brackets - 1 too many at the end, one toofew after the end of the first Sumproduct formula. Unless you have 12* as text in your column, the "12*" won't work. Just plain =12 will pick up all the occurrences of 12 =SUMPRODUCT(--('Imported Data'!$A$2:$A$30000="12*"), --(MONTH('Imported Data'!$I$2:$I$30000)=H5), --(YEAR('Imported Data'!$I$2:$I$30000)=J5)) -SUMPRODUCT(--('Imported Data'!$C$2:$C$30000=6), --(MONTH('Imported Data'!$I$2:$I$30000)=H5), --(YEAR('Imported Data'!$I$2:$I$30000)=J5)) -- Regards Roger Govier "vipa2000" wrote in message ... I have he following code. i want to count all the 12's in columnA range 2-30000 on a month picked in cell H5 and a year J5 and then deduct those records where priority = 6 and month =h5 and year =j5. Just cannot get the thing to work. i am sure it is the brackets. =SUMPRODUCT(--('Imported Data'!$A$2:$A$30000="12*"), --(MONTH('Imported Data'!$I$2:$I$30000)=H5), --(YEAR('Imported Data'!$I$2:$I$30000)=J5)) -SUMPRODUCT(--('Imported Data'!$C$2:$C$30000=6), --(MONTH('Imported Data'!$I$2:$I$30000)=H5), --(YEAR('Imported Data'!$I$2:$I$30000)=J5)) thanks in advance -- Regards vipa |
|
#3
|
|||
|
|||
|
I am not a hundred percent sure this is what you want, but do you also want
the priority 6 ones to have the 12 in column A? if so try =SUMPRODUCT(--('Imported Data'!$A$2:$A$30000="12*"),--(MONTH('Imported Data'!$I$2:$I$30000)=H5),--(YEAR('Imported Data'!$I$2:$I$30000)=J5),--('Imported Data'!$C$2:$C$30000<6)) in not try =SUMPRODUCT(--('Imported Data'!$A$2:$A$30000="12*"),--(MONTH('Imported Data'!$I$2:$I$30000)=H5),--(YEAR('Imported Data'!$I$2:$I$30000)=J5))-SUMPRODUCT(--('Imported Data'!$C$2:$C$30000=6),--(MONTH('Imported Data'!$I$2:$I$30000)=H5),--(YEAR('Imported Data'!$I$2:$I$30000)=J5)) You were right it was brackets. "vipa2000" wrote: I have he following code. i want to count all the 12's in columnA range 2-30000 on a month picked in cell H5 and a year J5 and then deduct those records where priority = 6 and month =h5 and year =j5. Just cannot get the thing to work. i am sure it is the brackets. =SUMPRODUCT(--('Imported Data'!$A$2:$A$30000="12*"),--(MONTH('Imported Data'!$I$2:$I$30000)=H5),--(YEAR('Imported Data'!$I$2:$I$30000)=J5)-SUMPRODUCT(--('Imported Data'!$C$2:$C$30000=6),--(MONTH('Imported Data'!$I$2:$I$30000)=H5),--(YEAR('Imported Data'!$I$2:$I$30000)=J5))) thanks in advance -- Regards vipa |
|
#4
|
|||
|
|||
|
bj and roger, thanks for input. I used the below.
=SUMPRODUCT(--('Imported Data'!$A$2:$A$30000="12"),--(MONTH('Imported Data'!$I$2:$I$30000)=H5),--(YEAR('Imported Data'!$I$2:$I$30000)=J5))-SUMPRODUCT(--('Imported Data'!$C$2:$C$30000=6),--(MONTH('Imported Data'!$I$2:$I$30000)=H5),--(YEAR('Imported Data'!$I$2:$I$30000)=J5)) It is returning a negative figure. Can i use two instances of sumproduct. I presume so as neither said i couldn't. I have double checked my data and I am still getting negatives -- Regards vipa "bj" wrote: I am not a hundred percent sure this is what you want, but do you also want the priority 6 ones to have the 12 in column A? if so try =SUMPRODUCT(--('Imported Data'!$A$2:$A$30000="12*"),--(MONTH('Imported Data'!$I$2:$I$30000)=H5),--(YEAR('Imported Data'!$I$2:$I$30000)=J5),--('Imported Data'!$C$2:$C$30000<6)) in not try =SUMPRODUCT(--('Imported Data'!$A$2:$A$30000="12*"),--(MONTH('Imported Data'!$I$2:$I$30000)=H5),--(YEAR('Imported Data'!$I$2:$I$30000)=J5))-SUMPRODUCT(--('Imported Data'!$C$2:$C$30000=6),--(MONTH('Imported Data'!$I$2:$I$30000)=H5),--(YEAR('Imported Data'!$I$2:$I$30000)=J5)) You were right it was brackets. "vipa2000" wrote: I have he following code. i want to count all the 12's in columnA range 2-30000 on a month picked in cell H5 and a year J5 and then deduct those records where priority = 6 and month =h5 and year =j5. Just cannot get the thing to work. i am sure it is the brackets. =SUMPRODUCT(--('Imported Data'!$A$2:$A$30000="12*"),--(MONTH('Imported Data'!$I$2:$I$30000)=H5),--(YEAR('Imported Data'!$I$2:$I$30000)=J5)-SUMPRODUCT(--('Imported Data'!$C$2:$C$30000=6),--(MONTH('Imported Data'!$I$2:$I$30000)=H5),--(YEAR('Imported Data'!$I$2:$I$30000)=J5))) thanks in advance -- Regards vipa |
|
#5
|
|||
|
|||
|
roger please see answer to bj
-- Regards vipa "Roger Govier" wrote: Hi You do have a problem with your brackets - 1 too many at the end, one toofew after the end of the first Sumproduct formula. Unless you have 12* as text in your column, the "12*" won't work. Just plain =12 will pick up all the occurrences of 12 =SUMPRODUCT(--('Imported Data'!$A$2:$A$30000="12*"), --(MONTH('Imported Data'!$I$2:$I$30000)=H5), --(YEAR('Imported Data'!$I$2:$I$30000)=J5)) -SUMPRODUCT(--('Imported Data'!$C$2:$C$30000=6), --(MONTH('Imported Data'!$I$2:$I$30000)=H5), --(YEAR('Imported Data'!$I$2:$I$30000)=J5)) -- Regards Roger Govier "vipa2000" wrote in message ... I have he following code. i want to count all the 12's in columnA range 2-30000 on a month picked in cell H5 and a year J5 and then deduct those records where priority = 6 and month =h5 and year =j5. Just cannot get the thing to work. i am sure it is the brackets. =SUMPRODUCT(--('Imported Data'!$A$2:$A$30000="12*"), --(MONTH('Imported Data'!$I$2:$I$30000)=H5), --(YEAR('Imported Data'!$I$2:$I$30000)=J5)) -SUMPRODUCT(--('Imported Data'!$C$2:$C$30000=6), --(MONTH('Imported Data'!$I$2:$I$30000)=H5), --(YEAR('Imported Data'!$I$2:$I$30000)=J5)) thanks in advance -- Regards vipa |
|
#6
|
|||
|
|||
|
i have found when i use the below code (the shortened version for error
trapping) that it returns 0. I don't understand why. H5 and j5 do work as they are used by other formula. Will sum product find data in the form of 120260? =SUMPRODUCT(--('Imported Data'!$A$2:$A$30000="12"),--(MONTH('Imported Data'!$I$2:$I$30000)=H5),--(YEAR('Imported Data'!$I$2:$I$30000)=J5)) -- Regards vipa "vipa2000" wrote: I have he following code. i want to count all the 12's in columnA range 2-30000 on a month picked in cell H5 and a year J5 and then deduct those records where priority = 6 and month =h5 and year =j5. Just cannot get the thing to work. i am sure it is the brackets. =SUMPRODUCT(--('Imported Data'!$A$2:$A$30000="12*"),--(MONTH('Imported Data'!$I$2:$I$30000)=H5),--(YEAR('Imported Data'!$I$2:$I$30000)=J5)-SUMPRODUCT(--('Imported Data'!$C$2:$C$30000=6),--(MONTH('Imported Data'!$I$2:$I$30000)=H5),--(YEAR('Imported Data'!$I$2:$I$30000)=J5))) thanks in advance -- Regards vipa |
|
#7
|
|||
|
|||
|
Hi Vipa
You could try =SUMPRODUCT(--(Left('Imported Data'!$A$2:$A$30000,2)="12"),--(MONTH('Imported Data'!$I$2:$I$30000)=H5),--(YEAR('Imported Data'!$I$2:$I$30000)=J5)) -- Regards Roger Govier "vipa2000" wrote in message ... i have found when i use the below code (the shortened version for error trapping) that it returns 0. I don't understand why. H5 and j5 do work as they are used by other formula. Will sum product find data in the form of 120260? =SUMPRODUCT(--('Imported Data'!$A$2:$A$30000="12"),--(MONTH('Imported Data'!$I$2:$I$30000)=H5),--(YEAR('Imported Data'!$I$2:$I$30000)=J5)) -- Regards vipa "vipa2000" wrote: I have he following code. i want to count all the 12's in columnA range 2-30000 on a month picked in cell H5 and a year J5 and then deduct those records where priority = 6 and month =h5 and year =j5. Just cannot get the thing to work. i am sure it is the brackets. =SUMPRODUCT(--('Imported Data'!$A$2:$A$30000="12*"),--(MONTH('Imported Data'!$I$2:$I$30000)=H5),--(YEAR('Imported Data'!$I$2:$I$30000)=J5)-SUMPRODUCT(--('Imported Data'!$C$2:$C$30000=6),--(MONTH('Imported Data'!$I$2:$I$30000)=H5),--(YEAR('Imported Data'!$I$2:$I$30000)=J5))) thanks in advance -- Regards vipa |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Sumproduct | Excel Worksheet Functions | |||
| sumproduct causing memory errors? | Excel Worksheet Functions | |||
| Can I reference =, <, or > sign in SUMPRODUCT | Excel Discussion (Misc queries) | |||
| Sumproduct function not working | Excel Worksheet Functions | |||
| adding two sumproduct formulas together | Excel Worksheet Functions | |||
Linear Mode
