Free Microsoft Excel 2013 Quick Reference

COUNTIFS with an OR?

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)


Post your answer or comment

comments powered by Disqus
SUM(IF()) as an array function instead of COUNTIF() with AND or OR criteria in Excel

Hello everyone,

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

I started my Countif as this: =COUNTIF(J1:J125,"Non-Exempt")

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!

Hi,

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

Hi, My name is Mick and I'm new to the forum. Although I have a resolution to my problem I am looking for something with a bit more finesse! In essence I have 2 columns, one text and the other numeric. I want to be able to select a group of values from the text column and find the average numeric value. I've used Countif to count the text and Sumif to add the values, followed by the sumresult / the count result. Something like:

=(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

Help is greatly needed! I have been working on this formula for several weeks and I still cannot come up with a solution!

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!

I Have a SUMPRODUCT function which is working fine to return a count of rows
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

I currently have the formula:
=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,

Hi

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 can not get the syntax correct to use countif with right or mid functions.
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

I received some great help on my last question and know need to make it a
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?

hello everydody
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

I have searched through the database and see that many people have been where I am and want to use COUNTIF with AND to link multiple conditions. I have read that you can use SUMPRODUCT in this case but I cannot get it to work with my scenario. Here we go...

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.

Is this possible to do? I know how to do an If with AND:

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.

I have a three column sheet. named MAIN
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 have a calendar with the dates in a row

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?

Hi,

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?

I have a SUMIF statement that doesn't appear to be working -- not throwing an
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.

I've got a spreadsheet I use to track quality scores, it will appear like such, and with 2003 I don't have AVERAGEIF()

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 stumped. It seems like I have tried ever feasible combination with no joy. I dug into a few other options such as DSUM and was reading into array and pivot, but I am too far from being knowlegeable enough to go there.

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.

The COUNTIFS function seems to do an 'AND' between each clause.
I am looking for something similar which does an 'OR' between the clauses instead.

Any ideas would be much appreciated...
Thanks
Jay

Finding unique values using 2 conditions and UIDs with multiples or subsequent values -- combination of INDEX MATCH FREQ ROW INDIRECT

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:
	
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..........001 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
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.

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


	VB:
	
 
{=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
This produces the serial numbers correctly unless there is a duplicate part number, where it repeats the first serial number found.

Please, your expedient assistance would be extremely appreciated.

V/r,
M

Sample Workbook:
test.xlsx

Hello there,

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?

Help again!
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:
	
((Q2) = "#Value!",0,(Q2)) 

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


	VB:
	
((Q2) = Error(#Value!),0,(Q2)) 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Or is there some other way to get around the error?

Thanks again for any assistance!


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