Sum & If Statement

**mrsdoubtfire** · 03-21-2010, 02:21 PM

Hello,

I have managed to get excel to look for a text string in a particular column on a different sheet using this formula:

=SUM(IF(JUNE!B:B=+A6,1,0))

The text string I am looking for is in the cell 'A6' - and for every occurrence of the string it finds, the result is incremented by 1.

What I want to do now is get it to look in other columns in other sheets. I would be massively grateful if anyone can tell me how to add other columns to this formula!

Cheers

**zbor** · 03-21-2010, 02:23 PM

How about =COUNTIF(JUNE!B:B,A6)

and repeat for other sheets

**mrsdoubtfire** · 03-22-2010, 06:33 AM

Hi Zbor, thanks for that.

I'm pretty new to this so forgive me...

I need to amend the formula I have so that excel will look in multiple columns for the text string - including the JUNE!B:B column that I already have in there. I just don't know how to add them into the formula if you see what I mean!

=SUM(IF(JUNE!B:B=+A6,1,0))

Thanks

**teylyn** · 03-22-2010, 07:07 AM

Hi mrsdoubtfire,

if your columns are consecutive, you could use something like

=SUM(IF(June!B:D=A6,1,0))

You don't need the + sign before the A6 cell address, by the way. And, as your first formula, this is an array formula and needs to be confirmed with CTRL-SHIFT-ENTER.

Or, much faster with Countif:

=COUNTIF(June!$B:$D,A6)

If your columns are not consecutive, you can use

=COUNTIF(June!$B:$B,A6)+COUNTIF(June!$E:$E,A6)+COUNTIF(June!$L:$L,A6)

The array formula equivalent of this would be

=SUM(IF((June!B:B=A6)+(June!E:E=A6)+(June!L:L=A6),1,0))

confirmed with CSE, but, to say it again: the Countif is much faster!!