|
05-12-2018, 01:26 PM
|
#1
|
Registered User
Join Date: Dec 2004
Location: 45th parallel
Posts: 2,178
|
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 210,"ABHZ",C1:C210,"*1970* "))
__________________
Best Don
Last edited by wilderness; 05-12-2018 at 01:28 PM.
|
|
|
05-12-2018, 01:42 PM
|
#2
|
Registered User
Join Date: Mar 2007
Posts: 1,994
|
Quote:
Originally Posted by wilderness
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 210,"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
|
|
|
05-12-2018, 01:55 PM
|
#3
|
dGnr8
Join Date: Aug 2003
Location: Niagara, Ontario
Posts: 3,023
|
Quote:
Originally Posted by Poindexter
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
__________________
.
The great menace to progress is not ignorance but the illusion of knowledge - Daniel J. Boorstin
The takers get the honey, the givers sing the blues - Robin Trower, Too Rolling Stoned - 1974
|
|
|
05-12-2018, 04:34 PM
|
#4
|
@TimeformUSfigs
Join Date: Jan 2002
Location: Moore, OK
Posts: 46,828
|
Quote:
Originally Posted by wilderness
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 210,"ABHZ",C1:C210,"*1970* "))
|
Can you give an example of what you are trying to accomplish with actual data?
|
|
|
05-12-2018, 04:56 PM
|
#5
|
Registered User
Join Date: Dec 2004
Location: 45th parallel
Posts: 2,178
|
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./
__________________
Best Don
Last edited by wilderness; 05-12-2018 at 04:59 PM.
|
|
|
05-12-2018, 05:04 PM
|
#6
|
Registered User
Join Date: Dec 2004
Location: 45th parallel
Posts: 2,178
|
cj,
Here's an existing sum (lower page of sheet) that simply provides a sum of COL D
=SUMIF(B1:B210,"ABHZ",D1 210)
I just need to add the 1970 (and each year afterwards; on separate lines) criteria to an additional line
__________________
Best Don
|
|
|
05-13-2018, 03:17 PM
|
#7
|
dGnr8
Join Date: Aug 2003
Location: Niagara, Ontario
Posts: 3,023
|
Based on this
Quote:
Originally Posted by wilderness
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,D1210,"ABHZ",C1:C210,"*1970* "))
|
should be =SUMIFS(D1 210,B1:B210,"ABHZ",C1:C210,"*1970* "))
sum_range and range are reversed between SUMIF and SIMIFS
__________________
.
The great menace to progress is not ignorance but the illusion of knowledge - Daniel J. Boorstin
The takers get the honey, the givers sing the blues - Robin Trower, Too Rolling Stoned - 1974
Last edited by Red Knave; 05-13-2018 at 03:19 PM.
|
|
|
05-13-2018, 06:17 PM
|
#8
|
@TimeformUSfigs
Join Date: Jan 2002
Location: Moore, OK
Posts: 46,828
|
Quote:
Originally Posted by wilderness
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.
|
|
|
05-24-2018, 09:12 AM
|
#9
|
what an easy game.
Join Date: Dec 2002
Posts: 43,096
|
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
__________________
Peace on earth, good will to all
GOD BLESS AMERICA
" I pass with relief from the tossing sea of cause and theory to the firm ground of result and fact"
Winston Churchill
Last edited by formula_2002; 05-24-2018 at 09:20 AM.
|
|
|
|
Posting Rules
|
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
HTML code is Off
|
|
|
|
|