Help using sumif/countif with two conditions

[Hollywoood]

Hi all,

Ive read a few posts on using arrays or others tricks in getting this accomplished but I think one of my conditions may be causing a slight syntax issue that I cannot find.

Below is the latest I've tried without returning the desired result, I will attempt to explain in plain english what it is I am trying to do

=SUM(IF('Data Master'!N:N>=1234,IF('Data Master'!R:R="*"&A2&"*",1,0),0))

My goal is to sum Column N of the sheet Data Master, where the cell value is greater than or equal to 1000 AND where on the same sheet, in the coresponding cell in column R, there exists the Name (the cell could contain several sames separated by a comma) of my summary.

Have to clarify further if Ive made it overly complicated. My two conditions are basically a >= check and a check for the presence of a string of text. Where both are true, I would like to sum

 

[T. Valko]

Hi all,

Ive read a few posts on using arrays or others tricks in getting this accomplished but I think one of my conditions may be causing a slight syntax issue that I cannot find.

Below is the latest I've tried without returning the desired result, I will attempt to explain in plain english what it is I am trying to do

=SUM(IF('Data Master'!N:N>=1234,IF('Data Master'!R:R="*"&A2&"*",1,0),0))

My goal is to sum Column N of the sheet Data Master, where the cell value is greater than or equal to 1000 AND where on the same sheet, in the coresponding cell in column R, there exists the Name (the cell could contain several sames separated by a comma) of my summary.

Have to clarify further if Ive made it overly complicated. My two conditions are basically a >= check and a check for the presence of a string of text. Where both are true, I would like to sum

IF functions don't support the use of wildcards but there are other methods that can be used.

What version of Excel are you using?

 

[iDeals]

you can you use a IF(And( statement

 

[dcamos]

=sum(if(and(n:n>1000,isnumber(find("text",cell))),rangetosum,"")
Ctrl+Shift+Enter

 

[Hollywoood]

Thanks guys for the fast reply

T. Valko - I am using 2003.

Dcamos - Thanks for the tip, I will give it a shot.

 

[Hollywoood]

dcamos, I've tried substituting into your outline with the following, my guess is i am still not coding the text portion correctly. I have the below

=SUM(IF(AND(Sheet1!N:N>=1000,ISNUMBER(FIND(A:A,Sheet1!R:R))),Sheet1!N:N,""))

where A:A is in the list of individuals and (on the sheet I am entering the formula) and Sheet1 column R is the master sheet which will potential contain that string as well as others in each cell.

Ive tried to cut and paste below (sorry for the format) but it gives a basic illustration of what I am trying to do. For each count cell, id like to have to number of times the Individual (on the same row) appears in the right hand side "individuals" cloumn, where the "Amount" value is greater than or equal to 1000. Likewise for sum, the sum of "amount" where the value (string) in the left hand "individual" cloumn exists in the right hand "individuals" column.

<TABLE style="WIDTH: 344pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=459 border=0 x:str><COLGROUP><COL style="WIDTH: 59pt; mso-width-source: userset; mso-width-alt: 2889" width=79><COL style="WIDTH: 48pt" span=4 width=64><COL style="WIDTH: 93pt; mso-width-source: userset; mso-width-alt: 4534" width=124><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 59pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=79 height=17>Individual</TD><TD class=xl22 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 48pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=64>Count</TD><TD class=xl22 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 48pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=64>Sum</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 48pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=64></TD><TD class=xl22 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 48pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=64>Amount</TD><TD class=xl22 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 93pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=124 x:str="individuals "> individuals </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>one</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right x:num>0</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"> one, two</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17 x:str="two ">two </TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right x:num>1000</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"> four, five, three</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>three</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right x:num>5000</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"> one, four</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>four</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right x:num>500</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"> one, two, three</TD></TR></TBODY></TABLE>

 

[dcamos]

I misunderstood part of your request. I pasted your chart into A1 in a spreadsheet and used these formulas to get count and sum.

B1=SUMPRODUCT(--(ISNUMBER(FIND(A2,F2:F5))),--(E2:E5>1000))
C1=SUMPRODUCT(--(ISNUMBER(FIND(A2,F2:F5))),--(E2:E5>1000),E2:E5)

 

[iDeals]

I'm still not 100% clear on what you're trying to do but looking at your formula I think the problem is with the Find() portion.

 

[Hollywoood]

Thank you so much my friend!

For my own knowledge, is there a limit to the conditions I can place in this manner, for instance if I would like to place a second criteria on the value of the number, a less than condition, would it be as simple as

B1=SUMPRODUCT(--(ISNUMBER(FIND(A2,F2:F5))),--(E2:E5>1000),--(E2:E5<75000))

 

[Hollywoood]

Thanks for looking at this iDeals. dcamos was able to get me to where I needed to be. Thank you though for your time as well!