Can countifs work with an "OR" in it? Seperating in the below formula the BO

& AY criteria into 2 countifs works but I would like to do it with just 1

countifs if possible.

Doesn't

work:=COUNTIFS(AMC!$C$2:$C$1796,OR($BO$67,$AY$67), AMC!$R$2:$R$1796,"="&$A85,AMC!$E$2:$E$1796,"="&CT$ 67)

Does

work:=COUNTIFS(AMC!$C$2:$C$1796,$BO$67,AMC!$R$2:$R $1796,"="&$A85,AMC!$E$2:$E$1796,"="&CT$67)+COUNTIF S(AMC!$C$2:$C$1796,$AY$67,AMC!$R$2:$R$1796,"="&$A8 5,AMC!$E$2:$E$1796,"="&CT$67)

& AY criteria into 2 countifs works but I would like to do it with just 1

countifs if possible.

Doesn't

work:=COUNTIFS(AMC!$C$2:$C$1796,OR($BO$67,$AY$67), AMC!$R$2:$R$1796,"="&$A85,AMC!$E$2:$E$1796,"="&CT$ 67)

Does

work:=COUNTIFS(AMC!$C$2:$C$1796,$BO$67,AMC!$R$2:$R $1796,"="&$A85,AMC!$E$2:$E$1796,"="&CT$67)+COUNTIF S(AMC!$C$2:$C$1796,$AY$67,AMC!$R$2:$R$1796,"="&$A8 5,AMC!$E$2:$E$1796,"="&CT$67)

- Sum(if()) As An Array Function Instead Of Countif() With And Or Or Criteria
- Countif with an or condition
- COUNTIF with another IF, plus an OR?
- CountIFS using an OR function
- Average of range where there is an OR condition.
- Countif using OR
- SUMPRODUCT with an OR component
- SUMIFS with an OR criteria
- Filter a table with an OR logic based on columns values
- Combining countif and mid or right functions
- SUMPRODUCT WITH AN "OR" MAYBE????
- Countif with OR condition
- Want to use COUNTIF with AND... trying SUMPRODUCT
- If function with some "And" plus an "Or" criteria
- SUMIF/COUNTIF with an additional criteria
- Countif with an extending argument
- Countifs with OR functions
- SumIf with an Or
- Trying to imitate AVERAGEIF() with an OR() test.
- SUMIFS with multiple or/and/not Criteria
- COUNTIFS with OR instead of AND
- Finding unique values using 2 conditions and UIDs with multiples or subsequent values
- Call Macro With An Argument
- Referencing A Field With An Error In It

I have a question reguarding a small project im working on. I have weather data from our local stats site and it give me hourly data between jan 2010 to may 20 2011.

The file contains some data in a colum that looks like the following:

cloudy

cloudy and rainy

fog and rainy

fog

haze

cloudy and snow

clear

etc... (another several thousand lines)

Now what I would like to do is determine how many time something other then clear comes up. For example I would like to:

COUNTIF(A1:A50,OR("*rain*","*cloudy*",etc))

Now the above OR is not working, if i remove the OR and use a single criteria I get the proper result, but when i add the OR and another criteria as shown above i get 0 as a result. Notice that some lines of data contain both key words cloudy and rain so this is the reason i want to use the OR, i dont want to count both instances just one for that particular hour.

Please advise, thank you for your help.

Matt

Now I need to add to it. If column A = "Y25" and column J = "Non-Exempt" count it. And then for a 2nd countif, if column A = "Y25" and column J = "Exempt" OR "Exempt2" count it. Then I'll need the same thing for "Y01" in column A and then again for about 20 sheets with different data in column A.

I tried the first one myself and failed miserably with:

=IF(A2:A125="Y25",COUNTIF(J1:J125,"Non-Exempt"),0)

How do I formulate this and the 2nd one with an 'OR' thrown in?

Thank you!

I have tried searching the forums and google for an answer to do this but I cannot seem to find a solution to my problem.

I am using Excel 2007 and looking at a large amount of data. To simplify the problem I have built the Table below

A B

1 Jack

1 James

2 John

2 jack

2 james

1 james

I want to use Countifs to display the amount of times that Jack OR James are displayed in B AND 1 is displayed in Column 1.

I have tried to do this through, = countifs($A:$A, "=1", $B:$B, {"Jack", "James"})

however the result it returns only reviews the first in the array, in this case, Jack.

Is there any way to use a nested OR function within a Countifs function without having to create a separate column and using =if(OR(A1="Jack", A1="James"),1,0) and then using Countifs with =countifs($A:$A, "=1", $C:$C,1) as the data set I am using is very large and this would be very time consuming.

Any / all help would be appreciated!

Many thanks

Maudise

=(Sumif (range,textrange,"=red") + Sumif (range, textrange,"=blue"....))/(Countif(range,"red")+Countif(range,"blue)...)

I'd really like to use and Average formula but can't get it to work with an OR condition.

Any help would be much appreciated.

Mick

I have three criterias that must be met before the date is considered. I have a formula that does that:

=COUNTIFS($E$4:$E$4000,"Document-NTP Checklist",$F4:$F$4000,"Finished""&")+COUNTIFS($E$4:$E$4000,"Form-Entitlement Verification Form",$F4:$F$4000,"Finished""&")+COUNTIFS($E$4:$E$4000,"Approval-ApprovalSdm",$F4:$F$4000,"Active").

This count states that the Document is finished, the Form is finished, and the Approval is active. I now need to add a countif for the date. The date range is in G4:G4000. My problem is either the document or form could have this date. I do not want to double count(sometimes the document and form have the same date). I need a countif with an or statement where it will count if all conditions are met: document is finished and happend on 9/28/11 OR form is finished and happened on 9/28/11.

I am super stuck! Any help or guidance would be greatly appreciated. let me know if you need more info. Thanks!

meeting about 7 different criteria (e.g., --(A1:A4000="Yes"), etc.). To this

function I need to add a component which returns TRUE if at least one of

another dozen or so conditions are TRUE. So it's like putting in another

dozen sets, all wrapped in an OR function. I tried variations of

"--(OR(B1:B40001,C1:C40000, etc.)" and then array-entering the whole

SUMPRODUCT function, but that doesn't work. Any ideas?

TIA

=SUMIFS('Labor Reclasses'!$D$6:$D$8000,'Labor

Reclasses'!$M$6:$M$8000,Summary!$A4,'Labor

Reclasses'!$F$6:$F$8000,"Travel-Non-Dispatch",'Labor

Reclasses'!$F$6:$F$8000,"Travel"))

I want to change the last two criteria to an OR. Thus the value in column D

would be added for all cases where Column M matches Summary!A4, and Column F

is either "Travel-Non-Dispatch" or "Travel"

Thanks,

i have a table and have some columns.

i want to filter the table so that i just see the records which have "text1" in ther column "F" OR they have "text2" in ther column "H", for example.

How can i implement an OR filter ?

thanks in advance.

I have a long list of numbers in column A - sample:

404030164

404030173

404030182

404030192

404030204

I need to count the number of 1's in the last digit and 04's in the 2nd and

3rd digits.

--

|///

~ ~ //

( @ @ )

--oOOo-(_)-oOOo---

Charlie Woll

little more complex if you will.

Current Formula is:

=SUMPRODUCT((Sheet1!A2:A4=3)*(Sheet!B2:B4=100)*(Sheet!C2:C4="D")*Sheet1!D2:D4)

Now I would like it to do:

=SUMPRODUCT((Sheet!A2:A4=3 OR A OR 4 OR B OR 999999) and then continue on.

Is it possible to use an or statement in the formula or not?

DO YOU SEE MY POST??

DID I DO IT CORECTLY??

i have in column A many different data that are enter with validation list.

ex.

a1= corolla

a2= matrix

a3= camry

a4= tacoma

a5= tundra

i want to count the numbers of car and not the truck (here corolla, matrix and camry are the cars and tacoma and tundra are the trucks)

i try this : countif(a1:a5;or("corolla";"matrix";"camry"))

but i have a zero value return....

hope this is simple but i can not figure it out

thanks and happy holidays to everyone

Need to pull data from the following spreadsheet (obvy an example) to reflect the following:

Number of Voluntary terms with LOS less than 3 months (=.25 and .5)

When I try SUMPRODUCT I get a NUM error but don't know how to fix it. The columns used in the formula will be H (LOS at Term - in years) and J (Term Type). Help?

******** ******************** ************************************************************************>Microsoft Excel - 2008-0401 Termination Details.xls___Running: 11.0 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutE2E3E4E5=

ABCDEFGHIJ1RegionCompany/UnitLocationEffectiveÂ DateQuarterNameJobÂ TitleLOSÂ atÂ TermTermÂ ReasonTermÂ Type2RegionÂ #1CompanyÂ #1LocationÂ #1Â 01/07/20081stÂ QuarterTermÂ #1TitleÂ #10.92NewÂ JobÂ Voluntary3RegionÂ #2CompanyÂ #2LocationÂ #2Â 02/01/20081stÂ QuarterTermÂ #2TitleÂ #20.15PerformanceÂ Involuntary4RegionÂ #3CompanyÂ #3LocationÂ #3Â 02/08/20081stÂ QuarterTermÂ #3TitleÂ #30.23NewÂ JobÂ Voluntary5RegionÂ #4CompanyÂ #4LocationÂ #4Â 03/07/20081stÂ QuarterTermÂ #4TitleÂ #40.50NewÂ JobÂ VoluntaryTermDetails (2)Â

[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box

PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

IF(AND(criteria1,criteria2),True,False)

and I can do an OR:

IF(OR(criteria1,criteria2),True,False)

but can I do both? Thanks for any help.

COLUMNS:

a.) AGENT (Validated with a pulldown list)

b.) STATUS (Validated with a pulldown list)

c.) PRICE

I have another sheet named STATS

Cell STATS!B1 contains the agents name (Validated with a pulldown list)

Cell STATS!B2 I want to count how many active listings the agent in

STATS!B1 has

=COUNTIF(MAIN!A2:A61,STATS!B1)

Cell STATS!B3 I want to sum the entire value of all the active listings

agent in STATS!B1 has.

=SUMIF(MAIN!A2:A61,STATS!B1,MAIN!C2:C61)

AVAILABLE STATUS VALUES:

ACTV

DFT

CNT CS

CNT CC

CNT PS

CNT SI

PEND

WDRWN

SOLD

LEASED

EXP

Based on those values above, what I am hoping to create is a SUMIF and

COUNTIF tally based on the agents name where the STATUS equals ACTV, DFT,

CNT CS, CNT CC, CNT PS, CNT SI, or PEND.

I want to prevent values from being SUMMED and COUNTED if the status equals

WDRWN, SOLD, LEASED or EXP

I am not sure how to incorporate this additional requirement into the

corresponding formulae. Any assistance would be greatly appreciated.

-cypher

I want to count instances of an entry under the dates using 'countif' from 01JAN until today.

My problem is that today changes value [up by one] each day so the 'countif' function will need to extend its range to the right by one cell each day.

How do I do this?

I've run into a problem using countifs.

What i want the formula to do is look at 2 ranges and return a count but i want it to look for multiple values to count, not just one value.

i have attached a very simplified sheet of what i want to do. In the purple area i want to have a count of all the category A's and all the Category B's per person. So using this example attached 'jo's' count will be 4 cat A and 1 Cat B. Mark's count would be 4 Cat A and 0 Cat B. Deans counts would be 3 Cat A and 0 Cat B and so on......

Any help would be massively appreciated!

Kind regards

Richard

PS Forgot to add that what I kind of want is to embed an OR in my COUNTIFS to say so it totals up the count if the value is equal to this OR this OR this etc etc - Hope that makes sense. Is this possible?

error, but not returning the correct value:

=SUMIF(DataSheet!$C:$C,"61505 OR 61515 OR 61517",DataSheet!$E:$E)

What I'm trying to say is that if the value in column C is 61505 or 61515 or

61517, then it should sum the values in column E. I have not used a range

for C or E, just the columns, because the number of rows will change

Googling on this tells me that it's probably not possible with a SumIf --

and I tried SumProduct, but that didn't work either.

Suggestions? Thanks in advance.

name | team | score

joe f | billing | 3

may r | billing | 3

roy m | status | 1

kay t | first call | 1

etc...

I already used an array function to find the average of each team (even with empty scores) by using:

=AVERAGE(IF(DeptRange=$B$7,C$20:C$200)) [Ctrl-Shift-Enter]

EDIT: Incidentally, I changed AVERAGE() to COUNT(), and it kicked back 9 values instead of 3 (I have one value entered for each team atm).

B7 is the name of the department I'm finding the average for.

I've got 3 departments in one location, and 6 in another location. I'm trying to add OR() to give me our regional quality averages, and it's not working. I used:

=AVERAGE(IF(OR(DeptRange=$B$7,DeptRange=$B$9,DeptRange=$B11),C$20:C$200)) [Ctrl-Shift-Enter]

The end result is it just averaging everything instead of only the departments that match. Is this just a limitation of Excel 2003 or am I using OR() incorrectly?

I am trying to Count (COUNTIF) and Sum (SUMIFS) Column 'A' when Column 'B' is either "X" or "Y" or "Z" and Column 'C' is not "000" or "030" or "087" . . . . or "877" (a total of 45 exceptions) and Column 'D' is "1"

If I use an OR("X","Y","Z") it returns True or False and I get zero.

If I use ("X"+"Y"+"Z"), I get zero.

I have tried multiple formats with the exception list also with no joy. ("<>000","<>030", "<>087")

Here is the formula before the exception list is introduced:

=SUM(SUMIFS($A:$A, $B:$B,"X", $D:$D, "1"),SUMIFS($A:$A, $B:$B,"Y", $D:$D, "1"), SUMIFS($A:$A, $B:$B,"Z", $D:$D, "1"))

When I tried to use SUMIFS($A:$A, $B:$B, "X", $B:$B "Y", $B:$B, "Z", $D:$D, "1") it returned zero records.

The closest I have come listed each of the criteria in Column B (X, Y, Z) with the entire exception list of 45 items ("000", "030", "877" etc) and failed because it exceeded 255 characters. I even tried to splice it together using &""& or &","& but SUMIFS was not real happy.

I have also tried using a table array and it didnt come out right.

I must accept the data I am working with as text because the data in column 'C' can be alphanumeric.

Thanks for your time, in advance.

I am looking for something similar which does an 'OR' between the clauses instead.

Any ideas would be much appreciated...

Thanks

Jay

I have a set of data in a sheet: 'Matrix'.

Column F contains part names (contains multiples of each type--named the range matrix_pn).

Column A contains space vehicle numbers (Values = 1-12; Rows 2 - ~1000; range named matrix_sv).

Column H contains serial numbers (numbers or text; rows 2 - ~1000; named range matrix_sn).

There is a grid on another sheet: 'Summary Matrix'.

The grid is as such: Part number vertically in Column E (static data, matching names in column F of the Matrix sheet); horizontally, Columns G through R in Row 2 are the Space Vehicle Numbers 1-12.

VB:The solution for the grid is an array formula (that I cannot piece successfully on my own) that uses the part name (Col E) in the 'Summary Matrix' sheet to find unique serial numbers in the 'Matrix' sheet using 2 conditions: the Space Vehicle and the Part Name. Another complication is that the part name list in the Summary Matrix may have duplicates as well--when for example 2 parts of that type are on the vehicle.Sample Data: -- Matrix Sheet -- || Col A (SV #) || Col F (P/N) || Col H (S/N) || 1...................Tank.............2001xx 2...................Tank.............2002xx 3...................Tank.............2003xx 12..................Tank.............2012xx 12..................Wing.............001 1...................Wing.............002 2...................Wing.............100102 3...................Wing.............100103 -- Summary Matrix Sheet -- || Col E (P/N) || Col G (SV1) || (SV2) || ... || Col R (SV12) || Tank.............2001xx.........2002xx..........2012xx Wing.............002............100102..........001If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

I started to use this formula in Cell G4--the first cell of the grid:

VB:This produces the serial numbers correctly unless there is a duplicate part number, where it repeats the first serial number found.{=INDEX(matrix_sn,MATCH(1,(matrix_pn=$E11)*(matrix_sv=1),0))}If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

Please, your expedient assistance would be extremely appreciated.

V/r,

M

Sample Workbook:

test.xlsx

i want to create a lot of buttons, one in each line, that, when you click on them, open a file. The filename is in a cell in the same row as the button.

Of course every button should open a different file, and that is the problem. As far as i know (not very much) the buttons can only call other subs, and not with an argument.

What should i put into the "onAction" property of each button, so that each button opens a different file? I really dont want to create hundreds of subs just to get this done, there must be another way.

Thanks for helping me...

PS: By the way, how can i delete all but one buttons of a sheet with a vba script? Or shall I open another post for that?

My problem is I have a field when imported occasionally has a #Value! error in it. Most of the records are fine. As I will be importing it into another DB I need to have the #Value! removed and replaced with a Zero.

Here is what I tried.

I have a field that is displaying a #value! error. In another field that is referencing that field I wanted to do the following if statement. Basically if that error value is displayed show a zero, else show the value of the square. (Note I also tried it with an IIF but got a #Name? error)

VB:Or should I be looking for an error? When I ran it, It basically gave me the value of the Q2 field.((Q2) = "#Value!",0,(Q2))If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

VB:Or is there some other way to get around the error?((Q2) = Error(#Value!),0,(Q2))If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

Thanks again for any assistance!

No luck finding an answer? You could always try Google.