Countif with based on multiple criteria

Lukma

Board Regular
Joined
Feb 12, 2020
Messages
240
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
Hi friends

Please i was wondering if anyone could help out with a formula that could count based on multiple criteria

i need to count a range of column with different value the meet and do not meet that if ( Half equal 10 and Full equal 16) so i need to count are many that meet and do not meet for each criteria

here is my table appreciate if anyone can help me with a formula that can count based on multiple criteria

Book2
ABCDEFGHIJKLMNO
1
2FullHalf
31610
4MeetDo Not meet
5A-221Half9812192510101015
6A-221Full102515191614164512
7A-222Half9812192510101015
8A-223Full102515191614164512
9A-224Half9812192510101015
10A-225Half9812192510101015
11
Sheet1
 
Please i need to Ask supposing the value are in time format will it still count and ignore blanks cells

if it is possible i would prefer the time format count
I am not sure what you mean. Please post an example, and your expected output.

Note that there is also a COUNTIFS function, that allows for more than one condition.
See: MS Excel: How to use the COUNTIFS Function (WS)

If you wanted to check for a value less than some certain value, but greater than zero, you would just have two conditions, applied against the same range; one to check to see if it is greater than 0, and the other to see if it is less than your set number.

(Note that in Excel dates & times are really just numbers, with special date formatting. A date is really just the number of days since 1/0/1900, and time is just the fractional part of one day. This is why you can compare dates or times to 0).
 
Upvote 0

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"
thanks for your respond

ILSP Performance Tracking 2020.xlsx
OPQRSTUABACADAEAFAGAHAIAJAKALAMANAOAPAQARASATAUAVAWAXAYAZBABBBCBDBEBFBGBHBIBJBKBLBMBNBOBPBQBRBSBTBUBVBWBXBYBZCA
7Total Monthly Visit Trips66HalfFull
8Total Meet Target KPI4467%
9Total Exceed Target KPI2233%
10Full Half
111610
13VesselsMeet KPI Target Exceed KPI TargetVisitMeet KPI Jackup /Barge Exceed KPI Jackup /Barge VisitSupply Vessel'sP&D VSLVoyage Mus NoMsft PlanAsseifiya Island Ettouk Island Al Ghallan Island Umm Al AnbarBu Sikeen IslandAl Qatia Island Rig Al IttihadRig JunanaRig Al GhallanRig Al BzoomRig DiyinaRig Al HailRig Al GharbiaRig Al LuluRig Al YasatRig High Island-7Rig PN-8Rig HudairiyatRig MakasibRig MarawwahRig MuhaiyimatRig QarninRig Butinah Rig ShuweihatRig SMS Faith Rig Sms Mariam Rig VKN-3Deep Driller-2Deep Driller-3Deep Driller-6Rig Artabhatt-1Compact DrillerBarge KawawaBarge BahiaBarge LulwaBarge Pride Barge Supporter Barge ShamalBarge SciroccoBarge ShahamaTotal Spent HrsIslandActual Carge PlanMeet KPI TargetExceed KPI Target Spent HoursMeet Full TargetExceed Half KPI Target
14ADNOC-1010415 ADNOC-1010D3508Half    3:308:18                                     2 11:48  
15ADNOC-101115116  ADNOC-1010D3524Half    4:2416:00                                   1120:24  
16ADNOC-22115116 ADNOC-1010D3570Full     4:58                                    1 4:58  
17ADNOC-222 11ADNOC-1010D3627Full                                               
Backup Date
Cell Formulas
RangeFormula
P7P7=SUMIFS(X14:X692,AE14:AE692,G9)
P8P8=SUMIFS(V14:V692,AE14:AE692,G9)
Q8:Q9Q8=P8/$P$7
P9P9=SUMIFS(W14:W692,AE14:AE692,G9)
P14:P17P14=IFERROR(SUMIFS($BW$14:$BW$692,$AB$14:$AB$692,$O14),"")
Q14:Q17Q14=SUMIFS($BX$14:$BX$692,$AB$14:$AB$692,$O14)
R14:R17R14=SUM(P14:Q14)
AD14:AD17AD14=IFERROR(INDEX($D$14:$D$60287,MATCH(0,IF($AB14=$E$14:$E$60287,IF($B$7=$B$14:$B$60287,COUNTIF($AD$13:$AD13,$D$14:$D$60287), "")), 0)),"")
AE14:AE17AE14=IFERROR(VLOOKUP(AD14,$DM$14:$DQ$6285,5,0),"")
AF15:BT17,AF14:BV14AF14=SUMIFS($L$14:$L$60287,$E$14:$E$60287,$AB14,$D$14:$D$60287,$AD14,$I$14:$I$60287,AF$13,$B$14:$B$60287,$B$7)
BW14:BW17BW14=IFERROR(COUNTIFS(AF14:AK14,"<="&$C$10,AF14:AK14,">0"&$B$10),"")
BX14:BX17BX14=COUNTIFS(AF14:AK14,"<="&$C$11,AF14:AK14,">"&$B$11)
BY14:BY17BY14=SUBTOTAL(9,AF14:AK14)
BZ14:BZ17BZ14=IFERROR(COUNTIFS(AL14:BS14,"<="&$G$10,AL14:BS14,">0"&$F$10),"")
CA14:CA17CA14=COUNTIFS(AL14:BS14,"<="&$F$11,AL14:BS14,">"&$G$11)
S15S15=COUNTIF(AF15:AK15,"<Z11")
T14:T16T14=COUNTIFS(AF14:AK14,"<="&$T$11,AF14:AK14,">"&$S$11)
Press CTRL+SHIFT+ENTER to enter array formulas.
 
Upvote 0
This appears to be very different than your original question. If so, then it should be posted to a new question.

In any event, your last question has no explanation. In these type of questions, you should post the following three things:
1. What your data looks like
2. A clear explanation of what you are trying to accomplish
3. What your expected result should look like

Also, unless you are 100% confident that you can take our answers and adapt them to your question, you should NOT simplify your question to the point where we answer the question you asked, but it doesn't really answer your actual problem (because you oversimplified it).
 
Upvote 0

Forum statistics

Threads
1,213,557
Messages
6,114,288
Members
448,563
Latest member
MushtaqAli

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top