COUNTIF two adjacent cells meet logical criteria - help please!

Hi!

I've got a large spreadsheet of text string data (around 500 rows by 20 columns).

First, I want to count the number of occurrences of a given text string occurring anywhere in the sheet.
This is easy to do with the COUNTIF statement. No problems...

Now my question:
Secondly, I need to count the number of occurrences where a given text string occurs anywhere on the sheet AND also a second (different) given text string occurs in the cell immediately to the right of each cell that contains the first text string.

This isn't my actual dataset (I'm not a crazed market gardener!) but I hope the following example illustrates what I'm trying to do:

beans peas tomatoes cabbages
cabbages lettuce peas beans
peas tomatoes cabbages lettuce

I need a function which will look at the entire sheet and COUNTIF ((a cell contains the text string "peas") AND (the cell immediately to its right contains the text string "tomatoes"))
- in this example the function should return the value "2"

I've tried using COUNTIF and VLOOKUP nested in various combinations with the logical AND command, but whereas COUNTIF only needs me to specify the entire data range, VLOOKUP seems to require a specific column index number, and my dataset doesn't allow for this.

Is it possible to do this in Excel 2007?
If so I'd be really grateful for any help you could give me in writing a function which will do this without me having to re-enter my entire dataset in a different format.

Many thanks in anticipation

Dave

another way....see attachment

Dear all

Many thanks for your prompt & very helpful responses.

Greatly appreciated, as the thought of having to trawl through the data manually and count the associations was bringing me out in a cold sweat!!

Must learn to write Visual Basic Macros...looks like a really powerful technique if I can get my head around it...

Thanks again for all your help

Dave

I'm trying to use wildcards with this and it isn't working as expected.

For example:
=CountIfAll(3,"Std3",C14:D37) returns the correct result for my spreadsheet: 2
but
=CountIfAll(3,"Std*",C14:D37) returns 0.

The latter formula should be returning at least a value of 2, I'm not sure what needs to be changed. . .

I think this might do it... This is from a spreadsheet that i made to count totals from one sheet that has the info listed week by week, but to render the totals in two-week sprints. Basically you are using the offset function then incrementing the columns by counting them using the counta function. So, as you drag the formula across, the counta functions calculates the next set of columns to sum.

=SUM(
COUNTIFS(

OFFSET('2017 PlanDetail'!$F$4,2,(COUNTA($E$26:O$26)*2)):OFFSET('2017 PlanDetail'!$F$4,50,(COUNTA($E$26:O$26)*2)),

ClientKeyLookUpTable[@Key]),

COUNTIFS(

OFFSET('2017 PlanDetail'!$F$4,2,COUNTA($E$26:O$26)*2+1):OFFSET('2017 PlanDetail'!$F$4,50,(COUNTA($E$26:O$26)*2+1)),

ClientKeyLookUpTable[@Key])

)

Hello Jon
Thank you for the "CountifAll" Code. I am a newbie in VBA.
I used your code but my table column as an error, so the code formula also gives an error.
i added the line "on error resume next" but that output is wrong.
Can you help me fix this!
Thank you.