I have the following.
BC33Pos test (dupes)Other field test34113512B34:C35Expression=IF(COUNTIF($B$34:$B$35,B34)>1,IF(COUNTIFS($B$34:$B$35,B34,$C$34:$C$35,"<>")>=2,TRUE,FALSE),FALSE)textNO
I would expect each cell to be highlighted, however C35 does not seem to be affected.
The theory behind...
Hiya all,
I'm struggling a bit with I have a list of customers who bought Product A (which is a sample of the main product) on one sheet, then I have a list of customers who bought Product B (which is the main product) on another sheet. Is there a way that I can (on a separate summary sheet)...
i have this table:
2022-101
101
1
2022-pzl
101
1
2020-201
102
1
2020-202
103
1
1
101
102
103
i want to conditional format Row 6 if the matching value in column b (ex. 101) is populated in column c and d even if on different rows. (ie: 101 yes,102 no, 103 yes).
i have...
Hi everyone,
I'm trying to count the amount of times 2 different cells have the same date and also the times in a 3rd cell need to match and the 4th cell need to be before/after a certain time.
In my formula I've only managed to put in the times, so if the date is incorrect it will be counted...
I am trying (without success) to use either Countifs or Sumproduct to count how many cancellations were made with less than 48 hours notice.
So, here Status = Cancelled and Start Date >= 01/11/2022 and Start Date <= 30/11/22 AND where the difference in start date and start time and cancellation...
Hi,
I have this matrix:
A
B
C
5
5
6
1
3
5
4
2
6
1
5
6
and I would like to count the number of "1", "2", ecc for each column:
A
B
C
1
2
0
0
1+2
2
1
0
4
1
0
0
6
0
0
3
So a kind of countifs where you can set "A" and "1" as parameters.
Any suggestion? :)...
I'm having a problem with a COUNTIFS function that I've been working with. The logic of it seems sound to me but once the date moves to 10/2/2022 things seem to break.
The idea is to see if a machine is running based on the information in the table to the right.
Can someone help me understand...
Hi so I'm trying to count in the 4 Bag Test column under Fail, (sheet: Performance Review - H5) if on (sheet: Officer Tracker - G5:J5)
That if they fail even one bag or more out of A, B, C or D that it will count 1 fail in H5 in performance review sheet.
Hope that makes sense and thank you as...
Hello,
I am trying to do the following with a COUNTIFS Formula
G:G = "In Survey"
AF:AF = "" (Blank)
AD:AD = <>"" (Populated with data, but not specific)
My original formula looked like this:
=COUNTIFS(Data!G:G,"In Survey",Data!AF:AF,"",Data!AD:AD,"<>""")
However with or without data in AD...
Kaya,
We have a Leave planner chart that counts leave for the month.
All leave codes appear from drop down list.
Then those figures are added to a totals page.
Current Sum
=COUNTA(C14:AG14)-((COUNTIF(C14:AG14,"H1")+COUNTIF(C14:AG14,"H2"))/2) - please note H1 & H2 are half days which is...
Hi everyone,
Every month I get this table (Clients) where I am given costs broken out by rows of clients and columns of weeks. However, I need to summarize these values by Client and Month instead of week (Sheet2).
Pivot tables don't seem to read across columns and my current sumproduct...
Hi, I am trying to do a few things based on the below sheet. Essentially I have a long sheet of dates broken down by year ,month ,day hour...etc. I aim to have a rolling count of the total number of rows for each year. So column "percentcounter is an example of what I would expect to see. It...
Hi all!
I am currently comparing values in two sheets to each other to see if they are similar or dissimilar.
I have this setup at the moment:
My formula is: =IF(COUNTIFS('[CONFIDENTIAL - NUMBER.xlsx]Sheet1'!$T$2:$T$1712;E2;'[CONFIDENTIAL - NUMBER.xlsx]Sheet1'!$R$2:$R$1712;A2)=1;"yes";"no")...
I'm using
=IF(COUNTIFS(Vendors[Supplier Name],[@Vendor],Vendors[PO Type],[@[PO Type]]),"Yes","No")
and it works great but only when both areas have text in them. I need it to still return a true value when both results match even when those cells are blank. Could anyone help? The PO type is...
Hi all, I am trying to write a formula where I can calculate all the 4a’s that are in the Gym, playing Basketball and are on a Tuesday. Hoping to find a formula where I would be able to drop down H3, H4, H5 to give me what I want at any time. Is it possible given that the dates are column...
Hello excel gurus,
I have googled long and far and wasn't able to find the solution to this basic problem.
I am trying to COUNTIF a range is greater than (-1,000,000). I noticed the number it was resulting in was too high.
I have a lot of zeros in the table, which seem to qualify for that...
Hi, I'm trying to count number of intake dates within a month but sometimes there will be mulitple intakes in one day so I just want to count each date once.
The data is part of a bigger spreadsheet which I can't change and contains data from previous and subsequent months.
In the sample...
Hi,
I have a table like the one below.
User
Current Stage
Stage 1 Date
Stage 2 Date
Stage 3 Date
A
Stage 2
15/2/2022
16/4/2022
B
Stage 1
10/1/2022
C
Stage 3
22/5/2022
26/6/2022
28/6/2022
D
Stage 1
14/4/2022
16/4/2022
E
Stage 2
15/1/2022
16/2/2022
18/4/2022
I am trying...
I'm looking for a solution to a troubling formula. I'm trying to count entries from the past month, past 3 months and the past year. I know I'm going wrong somewhere as the latter two categories are coming back as 0s, when they should at least be returning the same total as the first.
="Closed...
Hello Wise Excel Users,
I am in desperate need of your help, to both fix a shoddy bit of formula writing and put a stop the abuse my desk is receiving because of the trouble this is causing me.
The purpose of the code is to count items have been 'closed' today.
So say if Worker A has closed...
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.