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
How about =COUNTIF(JUNE!B:B,A6)
and repeat for other sheets
"Relax. What is mind? No matter. What is matter? Never mind!"
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
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!!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks