Sumifs (why won't it work?!)

[mr_hiboy]

Hi,


I'm trying to get sumifs to work, even on very simple example it's doesn't seem to want to play!


Simple file,

Cell A1 =A, A2 =B

Cell B1 =1, B2 =2


The following should give 3, but it's giving zero. If I run one criteria at a time it works.


=SUMIFS(B1:B2,A1:A2,A1,A1:A2,A2)


It's obviously something simple, but for the life of me I can't see it this morning! caffeine maybe required!


Thanks in advance

 

[Debraj]

Hi Hilboy,


In you sample file.. I am unable to find a single cell... where.. A1:A2 = A1 and also A1:A2 = A2


Did you find a single cell where A1 = A1 & A1 = A2..

Confused??.. lemme clear.. SUMIFS is workeed with AND condition.. and you are mixing this with OR condition..


try Below..

=SUMIFS(B1:B2,A1:A2,A1)+SUMIFS(B1:B2,A1:A2,A2)


or this..


=SUM(IF((A1:A2=A1)+(A1:A2=A2),B1:B2))

Regards,

Deb

 

[mr_hiboy]

Hi Deb,


Thanks for your reply.


A1 = A therefore sum column B which in this case is 1.


Both your alternatives work, but are work arounds, surely the sumifs should works without having to rewrite the sumif element? Trying to get my head around it.


Cheers

 

[Debraj]

Then try this also..

=SUM(SUMIF($A$1:$A$2,{"A","B"},$B$1:$B$2))