Help with SUMPRODUCT formula

%3CLINGO-SUB%20id%3D%22lingo-sub-1489056%22%20slang%3D%22en-US%22%3EHelp%20with%20SUMPRODUCT%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1489056%22%20slang%3D%22en-US%22%3E%3CP%3EI%20am%20trying%20to%20count%20the%20number%20of%20columns%20that%20meet%20three%20specified%20criteria%20using%20the%20SUMPRODUCT%20function.%20The%20columns%20contain%20the%20following%20variables%26nbsp%3B%20A%20%3D%20location(String)%2C%20D%20%3D%20New%20or%20Existing(string)%2C%20F%20%3D%20old%20allocation%20(integer)%2C%20G%20%3D%20new%20allocation%20(integer)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3ESUMPRODUCT(('Allocation%26gt%3BSpecials'!A%3AA%20%3D%20%24A24)*('Allocation%26gt%3BSpecials'!D%3AD%20%3D%20%24C%2423)*('Allocation%26gt%3BSpecials'!F%3AF%20%26gt%3B%20'Allocation%26gt%3BSpecials'!G%3AG))%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3EI%20want%20the%20third%20criteria%20to%20read%20%26gt%3B%202*the%20values%20in%20G%3A%20G.%20Is%20SUM%20product%20the%20right%20formula%2C%20if%20yes%2C%20how%20do%20I%20count%20the%20values%20in%20column%20F%20that%20are%20greater%20than%202%20times%20the%20value%20in%20column%20G.%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3EIf%20NO%20what%20other%20ways%20can%20I%20count%20the%20number%20of%20variables%20that%20meet%20the%20criteria%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3E%40members%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1489056%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1489292%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20with%20SUMPRODUCT%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1489292%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F710163%22%20target%3D%22_blank%22%3E%40victoria615%3C%2FA%3E%26nbsp%3BHey%20Please%20attach%20your%20excel%20File%20for%20example%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1489483%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20with%20SUMPRODUCT%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1489483%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F710163%22%20target%3D%22_blank%22%3E%40victoria615%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EDid%20you%20try%20third%20criteria%20as%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E('Allocation%26gt%3BSpecials'!F%3AF%20%26gt%3B%202*'Allocation%26gt%3BSpecials'!G%3AG)%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1489943%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20with%20SUMPRODUCT%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1489943%22%20slang%3D%22en-US%22%3ETry%20this%3A%3CBR%20%2F%3E%3DSUMPRODUCT((%E2%80%98Allocation%26gt%3BSpecials%E2%80%99!A%3AA%3DA24)*%3CBR%20%2F%3E(%E2%80%98Allocation%26gt%3BSpecials%E2%80%99!D%3AD%3DC23)*%3CBR%20%2F%3E(%E2%80%98Allocation%26gt%3BSpecials%E2%80%99!F%3AF%26gt%3B2*%E2%80%99Allocation%26gt%3BSpecials%E2%80%99!G%3AG))%3C%2FLINGO-BODY%3E
Occasional Visitor

I am trying to count the number of columns that meet three specified criteria using the SUMPRODUCT function. The columns contain the following variables  A = location(String), D = New or Existing(string), F = old allocation (integer), G = new allocation (integer)

 

SUMPRODUCT(('Allocation>Specials'!A:A = $A24)*('Allocation>Specials'!D:D = $C$23)*('Allocation>Specials'!F:F > 'Allocation>Specials'!G:G))

 

I want the third criteria to read > 2*the values in G: G. Is SUM product the right formula, if yes, how do I count the values in column F that are greater than 2 times the value in column G.

 

If NO what other ways can I count the number of variables that meet the criteria

 

@members

3 Replies

@victoria615 Hey Please attach your excel File for example

@victoria615 

Did you try third criteria as

('Allocation>Specials'!F:F > 2*'Allocation>Specials'!G:G)

 

Try this:
=SUMPRODUCT((‘Allocation>Specials’!A:A=A24)*
(‘Allocation>Specials’!D:D=C23)*
(‘Allocation>Specials’!F:F>2*’Allocation>Specials’!G:G))