I am trying to count the number of time an email appears on sheet 2 that also appears on sheet 1 that is from source computer on dates 5/6/20 and 5/7/20. I need to be able to change the date as needed to count this email match. The sheets update daily with new data so ideally the less manipulating of columns the better. Hoping for a single formula to complete this action. In my example the count should equal 2.
Example Question.xlsx
Sheet 1 has source - sheet2 does not have anysourceI am trying to count the number of time an email appears on sheet 2 that also appears on sheet 1 that is from source computer on dates 5/6/20 and 5/7/20.
So does that mean if on sheet1 it does not have 'computer' , then do not count at all
=IF(B2<>"computer", "", COUNTIFS(Sheet2!$A$2:$A$30,Sheet1!A2,Sheet2!$B$2:$B$30,">="&$F$2,Sheet2!$B$2:$B$30,"<="&$G$2))
you can change the dates in F2 and G2 - also change the range to suit any future additions
personally I dont like to refer to whole columns like Sheet2!$B:$B prefer a range
Wayne
if my assistance has helped, and only if you wish to , there is an "* Add Reputation" on the left hand side - you can add to my reputation here
If you have a solution to your thread - Please mark your thread solved do the following: >
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.
Yes the source is only on sheet 1 but I need a total count of emails on sheet 2 that appear on sheet 1 from source "computer". Ideally one cell with the total count. The date selector will work though, thank you!
so did that work OK
It counted the emails in a new column however, I need the count to appear in a single cell. Can I use the if statement instead of countifs?
EDIT
Also I should add that the emails won't always line up so matching the email row for row isn't an option.
Last edited by habitwestern790; 10-09-2022 at 04:23 PM.
i think may work - not tested fully
=SUMPRODUCT((B2:B7="computer")*(A2:A7=Sheet2!A2:A7)*(Sheet2!B2:B7>=Sheet1!F2)*(Sheet2!B2:B7<=Sheet1!G2))
May also be able to do with FILTER()
I get an Error: Array arguments to MULTIPLY are of different size.
I will look into filter and see if I am able to apply it.
are the ranges all the same
what version of excel do you have
its an array formula -
Windows version Ctrl+Shift+Enter
365 deals with arrays automatically
I am doing this in google sheets. Yes the ranges are the same, I am selecting the entire columns (minus the title row) because the sheets update with new data daily and I want it to be calculated as the new rows are added. I am putting this calculated on a third sheet as well, not sure if that matters.
sorry, i see you have posted in "other forum" - I dont know google sheets enough to be able to tell if it will work - Sorry
Thats okay, I am going to use your first formula and just do a countif of the 1's to get my number. You have been very helpful!
why not just a SUM() ??
unless emails in the list may not be unique - ???
would the same email still count as 1
One email shows that it appeared twice but I only want each email counted once so I am doing =countif(range, "1")+countif(range, "2").
in excel - "1" will look for numbers entered as text, so
=countif(range, 1)+countif(range, 2)
will be seen as a number
at least in excel
OR will changing to this help
=IF(B2<>"computer", "", IF(COUNTIFS(Sheet2!$A$2:$A$30,Sheet1!A2,Sheet2!$B$2:$B$30,">="&$F$2,Sheet2!$B$2:$B$30,"<="&$G$2)>0,1,""))
you have it covered anyway, so i should leave you to it really
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks