Horse Racing Forum - PaceAdvantage.Com - Horse Racing Message Board

Go Back   Horse Racing Forum - PaceAdvantage.Com - Horse Racing Message Board > Off Topic > Off Topic - Computers


Reply
 
Thread Tools Rate Thread
Old 05-12-2018, 01:26 PM   #1
wilderness
Registered User
 
wilderness's Avatar
 
Join Date: Dec 2004
Location: 45th parallel
Posts: 2,072
Rep Power: 16 wilderness is on a distinguished road
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,D1210,"ABHZ",C1:C210,"*1970* "))
__________________
Best Don

Last edited by wilderness; 05-12-2018 at 01:28 PM.
wilderness is offline   Reply With Quote Reply
Old 05-12-2018, 01:42 PM   #2
Poindexter
Registered User
 
Join Date: Mar 2007
Posts: 908
Rep Power: 12 Poindexter will become famous soon enough
Quote:
Originally Posted by wilderness View Post
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,D1210,"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
Poindexter is offline   Reply With Quote Reply
Old 05-12-2018, 01:55 PM   #3
Red Knave
dGnr8
 
Red Knave's Avatar
 
Join Date: Aug 2003
Location: Niagara, Ontario
Posts: 2,829
Rep Power: 18 Red Knave will become famous soon enoughRed Knave will become famous soon enough
Quote:
Originally Posted by Poindexter View Post
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
__________________
.
Life is forever uncertain and replete with vicissitudes
Red Knave is offline   Reply With Quote Reply
Old 05-12-2018, 04:34 PM   #4
cj
@TimeformUSfigs
 
cj's Avatar
 
Join Date: Jan 2002
Location: Moore, OK
Posts: 41,335
Rep Power: 64 cj is a name known to allcj is a name known to allcj is a name known to allcj is a name known to allcj is a name known to allcj is a name known to all
Quote:
Originally Posted by wilderness View Post
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,D1210,"ABHZ",C1:C210,"*1970* "))
Can you give an example of what you are trying to accomplish with actual data?
cj is offline   Reply With Quote Reply
Old 05-12-2018, 04:56 PM   #5
wilderness
Registered User
 
wilderness's Avatar
 
Join Date: Dec 2004
Location: 45th parallel
Posts: 2,072
Rep Power: 16 wilderness is on a distinguished road
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.
wilderness is offline   Reply With Quote Reply
Old 05-12-2018, 05:04 PM   #6
wilderness
Registered User
 
wilderness's Avatar
 
Join Date: Dec 2004
Location: 45th parallel
Posts: 2,072
Rep Power: 16 wilderness is on a distinguished road
cj,
Here's an existing sum (lower page of sheet) that simply provides a sum of COL D

=SUMIF(B1:B210,"ABHZ",D1210)

I just need to add the 1970 (and each year afterwards; on separate lines) criteria to an additional line
__________________
Best Don
wilderness is offline   Reply With Quote Reply
Old 05-13-2018, 03:17 PM   #7
Red Knave
dGnr8
 
Red Knave's Avatar
 
Join Date: Aug 2003
Location: Niagara, Ontario
Posts: 2,829
Rep Power: 18 Red Knave will become famous soon enoughRed Knave will become famous soon enough
Based on this
Quote:
Originally Posted by wilderness View Post
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(D1210,B1:B210,"ABHZ",C1:C210,"*1970* "))

sum_range and range are reversed between SUMIF and SIMIFS
__________________
.
Life is forever uncertain and replete with vicissitudes

Last edited by Red Knave; 05-13-2018 at 03:19 PM.
Red Knave is offline   Reply With Quote Reply
Old 05-13-2018, 06:17 PM   #8
cj
@TimeformUSfigs
 
cj's Avatar
 
Join Date: Jan 2002
Location: Moore, OK
Posts: 41,335
Rep Power: 64 cj is a name known to allcj is a name known to allcj is a name known to allcj is a name known to allcj is a name known to allcj is a name known to all
Quote:
Originally Posted by wilderness View Post
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.
cj is offline   Reply With Quote Reply
Old 05-24-2018, 09:12 AM   #9
formula_2002
what an easy game.
 
formula_2002's Avatar
 
Join Date: Dec 2002
Posts: 30,253
Rep Power: 46 formula_2002 is on a distinguished road
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
__________________
A lot easier to get lucky when house takes 1.41% from the payout rather than 15% to 25%"

Last edited by formula_2002; 05-24-2018 at 09:20 AM.
formula_2002 is offline   Reply With Quote Reply
Reply




Thread Tools
Rate This Thread
Rate This Thread:

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off

Forum Jump

» Advertisement
» Current Polls
Travers Stakes - Monomoy Girl Poll
Powered by vBadvanced CMPS v3.2.3

All times are GMT -4. The time now is 12:22 AM.


Powered by vBulletin® Version 3.8.9
Copyright ©2000 - 2018, vBulletin Solutions, Inc.
Copyright 1999 - 2018 -- PaceAdvantage.Com -- All Rights Reserved -- Best Viewed in a modern browser @ 1280x720 Resolution Or Higher
We are a participant in the Amazon Services LLC Associates Program, an affiliate advertising program
designed to provide a means for us to earn fees by linking to Amazon.com and affiliated sites.