sumif does not work if the critera is a cell which is the result of a formula??

[charles pho]

I want to sort through the check register for payee in column "B" and assign
a three character code to each check payee in column "D". In this example Im
seaching only for 'chris' pay. Cells in 'D' have 40 '=Ifs", one for each payee.

Then I wish to sum all the debits to each payee. In this example sum the pay for Chris.

In this case, the =sumif does not recongnise the 'CHR" in column 'D'. The =sumif
will work correctly if I type the 'CHR' into the appropriate cells but not if its
the result of a =IF(,,,,)!

This is contrary to all that Ive read on the sumif.


E2=SUMIF($D$1:$D$500,"CHR",$C$1:$C$500 ) > returns 0 unless CHR is manually typed in!

Ive attached a small example of the real spreadsheet so that anyone can help Id really appreciate it!

Thanks all

Charles,

[Tony Valko]

This part of your formula:

&IF(LEFT(B2,3)="AMA","AMA",IF(LEFT(B2,3)="IWV","IWV",IF(LEFT(B2,3)="Har","Harbot Freight"," ")))

Is concatenating a space charater to the end of the first part of the formula such that "CHR" is actually "CHR(space)".

In the formula in column D, instead of returning a space character return an empty string:

=IF(LEFT(B2,3)="Pay","CHR",IF(LEFT(B2,3)="Wal","WAL",IF(LEFT(B2,3)="Alb","Alb",IF(LEFT(B2,5)="The H","Hom",IF(LEFT(B2,3)="Gat","Ace",IF(LEFT(B2,3)="Iny","gas",""))))))&IF(LEFT(B2,3)="AMA","AMA",IF(LEFT(B2,3)="IWV","IWV",IF(LEFT(B2,3)="Har","Harbot Freight","")))

[charles pho]

Thanks Tony! It was driving me nuts,,,,excel-ently. I tested the sumif with a space after CHR and it started to work but I also noticed that I had the ranges unequal. Fixed that and that one sumif works. Im still a bit fuzzy on how the 'space' got included. Was it due to the '&' concatenation of the three pairs of IF groups? I will investigate further!

Charles