adding number of "if-exist" text in more than one

[robertpri]

I have three columns:
[red apple] [buy] [no]
[red apple] [buy] [no]
[red orange] [buy] [no]
[red apple] [sell] [yes]
[red apple] [sell] [no]
[blue apple] [buy] [yes]


[the brackets do not appear--they are just to display here]

I want to know how many times does 'red' AND 'buy' AND 'no' appear in the same row.

I've tried this:
=COUNTIF(A1:A6,"*red*")*COUNTIF(b1:b6,"*buy*")*COUNTIF(C1:C6="*no*")
but it multiples the result

and this

=COUNTIF(A1:A6,"*red*")+COUNTIF(b1:b6,"*buy*")+COUNTIF(C1:C6="*no*")
but it adds the result.

The result should be '3'.

and I cannot get wildcards to work in:
=sumproduct(--(A1:A6="red"),--(b1:b6="buy),--(c1:c6="no"))

The database is 10,000's of cells, and impossible to sort. I have key words in each column and need to see how many times they appear in the same row.

many many many thanks!

 

[martinee]

I'm not sure why the wildcards?

Try SumProduct:
=SumProduct((A1:A6="red")*(B1:B6="buy")*(C1:C6="no"))

HTH

 

[robertpri]

Re: adding number of "if-exist" text in more than

I need wildcards because the cells are
red apple
red orange
red grape

And wildcards don't seem to work in sumproduct

 

[robertpri]

Re: adding number of "if-exist" text in more than

To clarify, we have mega-cells with text, but there are key words within the text. I need to create a process to count the number of times these keywords appear in the same row. Unfortunately, it's three columns, and wildcards are the solution.

However, I've not been able to get COUNTIF to work because it either adds them up or mulitiples, and SUMPRODUCT does not appear to accept wildcards. [at least, I can't get them to work]

 

[drh7900]

I could also use help with this type of formula, but my request is slightly different because I want to sum a column, and not just count.

I am trying to sum all activity with a specific date and specific text in the range of data. For example:

1/1/07, Red 140, 300
1/1/07, Red 105, 500
1/1/07, Blue 120, 600
1/2/07, Red 130, 800
1/2/07, Blue 116, 900

If I could get the formula to work the way I wanted to, it would sum all data in the third column if the information in the 1st column is 1/1/07 and if "Red" is in the 3rd column. In this case, the total result would be 800.

I tried "=SUMPRODUCT((Activity!C2:C3006=Test!A3)*(SUMIF(Activity!G2:G3006,"*Deposits*",Activity!H2:I3006)))" Where Activity!C2:C3006 is the column containing the dates, where Test!A3 contains the specific date I'm looking for and where Activity!G2:G3006 contains the text I'm looking for and where Activity!H2:I3006 contains the values I need totalled. I know SUMIF isn't what I'm looking for, but I tried COUNTIF, and that does not give me the desired result either.

 

[robertpri]

drh7900

I finally solved my problem with an array formula. It's the only way I could get it to work.

{=SUM(IF($E$55:$E$513=C9,IF($G$55:$G$513=D9,$B$55:$B$513,0),0))}

In this case, rows E55 to E513 has the first column of data, G55 > G513 the second row, and B55 > B513 the third.

If the value of C9 [or whatever I put there] is found in E, and if my value in D9 matches the G column, then produce the added results in the B column.

I am no excel guy, but this works for me.

 

[Fitzhay]

Hi

Just for your information you can use sumproduct with FIND to achieve the same result

=SUMPRODUCT(($B$1:$B$7="buy")*(C1:C7="yes")*(ISNUMBER(FIND("red",A$1:A$7))))


Regards

 

[sean.ballard]

kinda the same...

Hi - I'm trying to do somethign similar with a customer list

I want to take data from sheet 2 and pull it into sheet 1. The trouble is the names don't match exactly

Using the name 'mcdonalds' as a hypothetical this works part way for me: =SUMPRODUCT(--(Data!$A$7:$A$500= "mcdonalds"),(Data!$B$7:$B$500))

To save myself a whole lot of editing, what I want it to do is pull mcdonalds, or something like *mcdon* from column B, and match it.

I've tried:

=SUMPRODUCT(--(Data!$A$7:$A$500= *MID(B3,1,10)*),(Data!$B$7:$B$500))

=SUMPRODUCT(--(ISNUMBER(FIND(B3,Data!$A$7:$A$500))))*(Data!$B$7:$B$500)

Neither worked

Does that make sense? Can anyone help?