Wildcarding cell references

[BritishBuddha]

I want a cell to be able to check each cell on row 1 using IF and if a specific value is present to COUNT/COUNTIF all cells from *3:*151 (where * denoted the coloumn of the cell checked)

Is wildcarding cell coloumns/rows possible?
I hope I've explained this correctly.

Thanks

 

[GlennUK]

This may work:

=SUMPRODUCT(--(A1:IV1="First check")*--(A3:IV151="Second check"))

 

[BritishBuddha]

Thanks for that.
Thought it was ok, but made an error. Doesn't seem to be working. Should it count each cell that contains the second check?

 

[BritishBuddha]

Apologies, the second check will contain text rather than a number, would it be possible to count 1 for each cell.

 

[GlennUK]

That's exactly what my example does.

 

[BritishBuddha]

Ahh, I assumed that I could use wildcards within the checks, is this possible?

 

[barry houdini]

Hello British Buddha,

You can't use a wildcard in SUMPRODUCT like you can in COUNTIF, you may be able to use another approach, is this related to your previous question, I replied belatedly there?

 

[BritishBuddha]

Hi Barry, I've just read your reply, thanks for your help, I managed to use Find and Replace and used the formula I was using for my first problem (or second problem, the first being that it would be a good idea for me to get my head around usign Excel )

This problem is worse!!!

I have numbers at A3:A151 (on Sheet1) and each column from B1 to IV1 contains a word. I want a cell (on another sheet) to check the first cell of each column B1, C1, D1, etc and if the word is present to check each cell within that column and count 1 each time a cell that contains another word.

Again, each cell contains four words, I'm only interested in one word though, it should disregard all others.

Cheers again!!

 

[barry houdini]

Do cells B1:IV1 also contain 4 words or just one? Can more than one column header contain the searched word?

 

[BritishBuddha]

B1:IV1 will contain one or two words and there will only be four options so there will definitely be repetitions.

This is what I'm looking at:
Sheet 1
http://i52.tinypic.com/4ptbfl.jpg

Current sheet
http://i53.tinypic.com/2ur136t.jpg

Cell B1 on the current sheet should check each cell on row 1 (in sheet1) when a cell contains the word 'wet' it should then check the cell two cells down, if that contains the term 'DBF' then it should add one.

Again, as seen on Sheet 1, the cell will contain four pieces of information.

Again, thank you!