sumproduct - deduct one from another.

V

vipa2000

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
 
R

Roger Govier

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 said:
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))
 
B

bj

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.
 
V

vipa2000

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
 
V

vipa2000

roger please see answer to bj
--
Regards vipa


Roger Govier said:
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



=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))
 
V

vipa2000

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))
 
R

Roger Govier

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))
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

sumproduct and percentage 1
Simplifying/speeding up this formula 5
Sumproduct and wildcards 18
One for somebody 0
Sumproduct to return a text 5
Sumproduct Help! 3
Countif/SumProduct 4
Sum with multiple criteria 6

Top