SUMIF/PRODUCT with multiple Criteria not working
 

Hi there

I have inherited a huge spreadsheet which runs a bunch of macros, is
protected (until I select unprotect), has multiple frame freezes,etc.

I have to tally up all the Consumables for a particular project based on a
certain grant code. I have tried using SUMIF(F21:F102,"14",AK21:AK102) but it
keeps returning 0 which is certainly not the case. (F$ is the column for
grant code and AK$ stores the dollar value of consumable.)

Am I using the wrong formula, or could it be as simple as a format error,
or....

Any suggestions?

Thanks

SUMIF/PRODUCT with multiple Criteria not working
 

Hi,

Might be dealing with extra spaces so maybe:

=SUM(IF(TRIM(F21:F102)="14",AK21:AK102))

enter using Ctrl+Shift+Enter

HTH
Jean-Guy

"Andi" wrote:

Hi there

I have inherited a huge spreadsheet which runs a bunch of macros, is
protected (until I select unprotect), has multiple frame freezes,etc.

I have to tally up all the Consumables for a particular project based on a
certain grant code. I have tried using SUMIF(F21:F102,"14",AK21:AK102) but it
keeps returning 0 which is certainly not the case. (F$ is the column for
grant code and AK$ stores the dollar value of consumable.)

Am I using the wrong formula, or could it be as simple as a format error,
or....

Any suggestions?

Thanks

SUMIF/PRODUCT with multiple Criteria not working
 

I presume column F contains numbers, normally you don't need quotes around
numbers unless they are text formatted so try

=SUMIF(F21:F102,14,AK21:AK102)

"pinmaster" wrote:

Hi,

Might be dealing with extra spaces so maybe:

=SUM(IF(TRIM(F21:F102)="14",AK21:AK102))

enter using Ctrl+Shift+Enter

HTH
Jean-Guy

"Andi" wrote:

Hi there

I have inherited a huge spreadsheet which runs a bunch of macros, is
protected (until I select unprotect), has multiple frame freezes,etc.

I have to tally up all the Consumables for a particular project based on a
certain grant code. I have tried using SUMIF(F21:F102,"14",AK21:AK102) but it
keeps returning 0 which is certainly not the case. (F$ is the column for
grant code and AK$ stores the dollar value of consumable.)

Am I using the wrong formula, or could it be as simple as a format error,
or....

Any suggestions?

Thanks

SUMIF/PRODUCT with multiple Criteria not working
 

Use SUMPRODUCT: =SUMPRODUCT(--(F21:F102=14),--(AK21:AK102))

Does that work?

Dave
--
Brevity is the soul of wit.


"Andi" wrote:

Hi there

I have inherited a huge spreadsheet which runs a bunch of macros, is
protected (until I select unprotect), has multiple frame freezes,etc.

I have to tally up all the Consumables for a particular project based on a
certain grant code. I have tried using SUMIF(F21:F102,"14",AK21:AK102) but it
keeps returning 0 which is certainly not the case. (F$ is the column for
grant code and AK$ stores the dollar value of consumable.)

Am I using the wrong formula, or could it be as simple as a format error,
or....

Any suggestions?

Thanks

SUMIF/PRODUCT with multiple Criteria not working
 

Adding TRIM before the array solved the problem.

Thanks!

"pinmaster" wrote:

Hi,

Might be dealing with extra spaces so maybe:

=SUM(IF(TRIM(F21:F102)="14",AK21:AK102))

enter using Ctrl+Shift+Enter

HTH
Jean-Guy

"Andi" wrote:

Hi there

I have inherited a huge spreadsheet which runs a bunch of macros, is
protected (until I select unprotect), has multiple frame freezes,etc.

I have to tally up all the Consumables for a particular project based on a
certain grant code. I have tried using SUMIF(F21:F102,"14",AK21:AK102) but it
keeps returning 0 which is certainly not the case. (F$ is the column for
grant code and AK$ stores the dollar value of consumable.)

Am I using the wrong formula, or could it be as simple as a format error,
or....

Any suggestions?

Thanks