• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

COUNTIF and SUMIFS

peterjack

New Member
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*")
 
Hi, @peterjack !

You can use SUMPRODUCT to work with 2003:

The countifs formula:

Option 1:
=SUMPRODUCT(--(I12:I1001="FLUID"), --(J12:J1001<>"VERIFIED"), --(LEFT(L12:L1001)="Y"))

Option 2:
=SUMPRODUCT((I12:I1001="FLUID")*(J12:J1001<>"VERIFIED")*(LEFT(L12:L1001)="Y"))

The sumifs formula:

Option 1:
=SUMPRODUCT(--(I12:I1001="FLUID"), --(J12:J1001<>"VERIFIED"), --(LEFT(L12:L1001)="Y"), G12:G1001)

Option 2:
=SUMPRODUCT((I12:I1001="FLUID")*(J12:J1001<>"VERIFIED")*(LEFT(L12:L1001)="Y")*G12:G1001)

I prefer the Option 1 in both formulas, for performance. Blessings!
 
Back
Top