Sum based on Multiple Criteria in separate sheet

[Matrix67]

Good day all,

I'm currently struggling with this conundrum...I need to transfer a figure from the end of a Row based on two criteria, column J= "1" & column O="Manufacturing, all this info being in a separate sheet, so far I have two formula's...both returning an incorrect result:

=SUM,'2012'!V3:V10000(IF('2012'!J3:J10000=1,IF('2012'!O3:O10000,("Manufacturing"))))

=SUMPRODUCT(('2012'!J3:J10000=1)*('2012'!O3:O10000="Manufacturing")*(V3:V10000))

Please Help...thank you all, oh & have a great weekend! Marco

 

[ilovepurple]

Which version of Excel are you using?

If you're using Excel for Windows 2007 or later, or Excel for Mac 2008 or later, you can make use of the SUMIFS formula as below:

=SUMIFS('2012'!V3:V10000, '2012'!J3:J10000,"=1",'2012'!O3:O10000,"Manufacturing")


Let me know if this works

 

[T. Valko]

Good day all,

I'm currently struggling with this conundrum...I need to transfer a figure from the end of a Row based on two criteria, column J= "1" & column O="Manufacturing, all this info being in a separate sheet, so far I have two formula's...both returning an incorrect result:

=SUM,'2012'!V3:V10000(IF('2012'!J3:J10000=1,IF('2012'!O3:O10000,("Manufacturing"))))

=SUMPRODUCT(('2012'!J3:J10000=1)*('2012'!O3:O10000="Manufacturing")*(V3:V10000))

Please Help...thank you all, oh & have a great weekend! Marco

The SUMPRODUCT formula is syntactically correct so it should return the correct result provided there is not a "data type mismatch" issue.

Using that syntax, if there are text entries in the sum range then you'll get a #VALUE! error. To get around that use this syntax:

=SUMPRODUCT(--('2012'!J3:J10000=1),--('2012'!O3:O10000="Manufacturing"),V3:V10000)

 

[Matrix67]

Guys, I can't thank you all enough...especially "ilovepurple"! Worked like an absolute CHARM! Have a fantastic weekend!!! I will now

 

[T. Valko]

Guys, I can't thank you all enough...especially "ilovepurple"! Worked like an absolute CHARM! Have a fantastic weekend!!! I will now

Good deal. Thanks for the feedback!