COUNTIF and SUMIFS in Excel 2003

[eriqp]

Okay, I've seen multiple posts on this but I am not having any luck trying an array with SUM and SUMPRODUCT.


I have multiple data fields but I want the count if I:I is equal to "FLUID", J:J is not equal to "VERIFIED", and L:L has any text that begins in "Y". Then the same for the sum.


The formulas I have in 2007 that work perfectly. Any suggestions on how to make it work with 2003?


=COUNTIFS(I12:I1001,"FLUID",J12:J1001,"<>VERIFIED",L12:L1001,"Y*")


=SUMIFS(G12:G1001,I12:I1001,"FLUID",J12:J1001,"<>VERIFIED",L12:L1001,"Y*")


When I take out the "<>" and the wildcards it seems to work. But I need those...

 

[Hui]

=Sumproduct((I12:I1001="FLUID")*(J12:J1001<>"VERIFIED")*(left(L12:L1001,1)="Y"))

and

=Sumproduct((I12:I1001="FLUID")*(J12:J1001<>"VERIFIED")*(left(L12:L1001,1)="Y"),(g12:g1001))


You may have to retype the " characters

 

[Krishnaoptif]

Hi Eriqp,


Please use the below formula in excel 2003 then you will Definitely get the right count of the data as per the given condition.


=SUMPRODUCT(($I$2:$I$1001="FLUID")*($J$2:$J$1001<>"VERIFIED")*(LEFT($L$2:$L$1001,1)="Y"))


Regards,

Krishna

9560305552

 

[eriqp]

Works beautifully. Thanks so much for your help!