Excel two col criteria

[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,D1210,"ABHZ",C1:C210,"*1970* "))

[Poindexter]

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,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

[Red Knave]

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

[cj]

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,D1210,"ABHZ",C1:C210,"*1970* "))

Can you give an example of what you are trying to accomplish with actual data?

[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./

[wilderness]

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

[Red Knave]

Based on this

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] )

=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

[cj]

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.

[formula_2002]

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