wilderness |
05-12-2018 01:26 PM |
Excel two col criteria
I'm attempting to use the criteria from two columns to SUM a third column, however one of the criteria columns is date (M/D/YYYY) and I'm only attempting to use the Year as criteria.
Tried multiple formulas and none work.
Think I'm close here, however it gives a weird Date result, rather than a SUM
=SUM(SUMIFS(B1:B210,D1:D210,"ABHZ",C1:C210,"*1970* "))
|
Poindexter |
05-12-2018 01:42 PM |
Quote:
Originally Posted by wilderness
(Post 2315825)
I'm attempting to use the criteria from two columns to SUM a third column, however one of the criteria columns is date (M/D/YYYY) and I'm only attempting to use the Year as criteria.
Tried multiple formulas and none work.
Think I'm close here, however it gives a weird Date result, rather than a SUM
=SUM(SUMIFS(B1:B210,D1:D210,"ABHZ",C1:C210,"*1970* "))
|
Will the year function help? I am not clear on what you are trying to do but it seems like if you add a column, convert the date with the year function into the new column, you should be able do to do what you are trying to do.
https://exceljet.net/excel-functions...-year-function
|
Red Knave |
05-12-2018 01:55 PM |
Quote:
Originally Posted by Poindexter
(Post 2315828)
Will the year function help?
|
If not, maybe change criteria2 to >12/31/1969 and then add range3 ,C1:C210 and add criteria3 <01/01/1971
|
Quote:
Originally Posted by wilderness
(Post 2315825)
I'm attempting to use the criteria from two columns to SUM a third column, however one of the criteria columns is date (M/D/YYYY) and I'm only attempting to use the Year as criteria.
Tried multiple formulas and none work.
Think I'm close here, however it gives a weird Date result, rather than a SUM
=SUM(SUMIFS(B1:B210,D1:D210,"ABHZ",C1:C210,"*1970* "))
|
Can you give an example of what you are trying to accomplish with actual data?
|
wilderness |
05-12-2018 04:56 PM |
I've a simple five column sheet (six if you count the 'notes column.
Col A is date of completion
Col B offers a mere name for four different data sources
Col C is date of publication (source)
Col D is number of text documents
Col E is number of images.
What I'm attempting to SUM is:
Col B is 'ABHZ' and Col C contains 1970 (note there will eventually be 1970 thru 1984 dates in this column) as the criteria,
while SUM for the item counts in both D & E (using above criteria).
FWIW; these are photo negatives and I simply wish a count by each year rather than an solitary overall count that I've already in place.
NOTE; these sums/counts are placed at the bottom of the sheet.
It's worth noting that Excel offers a 'begins with' option for data, however in this instance an 'ends with' (not available) would be ideal./
|
wilderness |
05-12-2018 05:04 PM |
cj,
Here's an existing sum (lower page of sheet) that simply provides a sum of COL D
=SUMIF(B1:B210,"ABHZ",D1:D210)
I just need to add the 1970 (and each year afterwards; on separate lines) criteria to an additional line
|
Red Knave |
05-13-2018 03:17 PM |
Based on this
Quote:
Originally Posted by wilderness
(Post 2315968)
Col A is date of completion
Col B offers a mere name for four different data sources
Col C is date of publication (source)
Col D is number of text documents
Col E is number of images.
|
your sum-range is wrong. You are summing B1:B210 instead of D1to D210
SUMIFS( sum_range, criteria_range1, criteria1, [criteria_range2, criteria2, ... criteria_range_n, criteria_n] )
Quote:
=SUM(SUMIFS(B1:B210,D1:D210,"ABHZ",C1:C210,"*1970* "))
|
should be =SUMIFS(D1:D210,B1:B210,"ABHZ",C1:C210,"*1970* "))
sum_range and range are reversed between SUMIF and SIMIFS
|
Quote:
Originally Posted by wilderness
(Post 2315968)
I've a simple five column sheet (six if you count the 'notes column.
Col A is date of completion
Col B offers a mere name for four different data sources
Col C is date of publication (source)
Col D is number of text documents
Col E is number of images.
What I'm attempting to SUM is:
Col B is 'ABHZ' and Col C contains 1970 (note there will eventually be 1970 thru 1984 dates in this column) as the criteria,
while SUM for the item counts in both D & E (using above criteria).
FWIW; these are photo negatives and I simply wish a count by each year rather than an solitary overall count that I've already in place.
NOTE; these sums/counts are placed at the bottom of the sheet.
It's worth noting that Excel offers a 'begins with' option for data, however in this instance an 'ends with' (not available) would be ideal./
|
I'm still a little confused but I'll look at this more closely and see if I can figure it out.
As for begins with, you can get something similar by using RIGHT.
IF A1 is 01012017, RIGHT(A1,4) would give you 2017.
|
formula_2002 |
05-24-2018 09:12 AM |
can use a bit of help here.
I need to automate the following excel routine;
select file 1 sheet 1
copy cells a1 through y1 and down through a1 to a26
select file 2 sheet 1
paste at cell a1
select "view"
run macro x
It would helpful if the routine uses access
to some extent I may need to edit the routine.
I have perhaps hundreds if not thousand of file to work on.
I do have a background using dbase 5 many years ago, so I'm sure I can work with the supplied routine.
my fingers thank you, my brain thanks you and my waking and sleeping hours thank you
|
All times are GMT -4. The time now is 02:07 AM. |
|
Powered by vBulletin® Version 3.8.9 Copyright ©2000 - 2024, vBulletin Solutions, Inc.
Copyright 1999 - 2023 -- PaceAdvantage.Com -- All Rights Reserved
|
|