Sum referencing help

[danny_p]

Help expanding criteria on this function please:

=SUMIF(January!$B:$B,B3,January!$D:$D)

I want to add criteria to the formula. Add the corresponding cells in column D that match column B like above, but now separate it with another name from column F.

Why does this not work:

=SUMIF(January!$B:$B,B3&(IF(January!$F:$F="Bill")),January!$D:$D)

The bold part is what i want to add.

 

[VoG]

Try

=SUMPRODUCT(--(January!$B1:$B100=B3),--(January!$F1:$F100="Bill"),January!$D1:$D100)

 

[danny_p]

OK thanks VoG, that worked. Now can i do the same only to count how many times this happens? how many times "Bill" and B# appear together...

Try

=SUMPRODUCT(--(January!$B1:$B100=B3),--(January!$F1:$F100="Bill"),January!$D1:$D100)

 

[VoG]

Yep. Try

=SUMPRODUCT(--(January!$B1:$B100=B3),--(January!$F1:$F100="Bill"))

 

[danny_p]

Awesome, works perfectly! thanks!