Help on a formula with 2 criteria’s, involving dates.

TexasEdge

New Member
Joined
Sep 20, 2006
Messages
2
My workbook consists of a “Jobs” sheet and a “Total Sheet” sheet. I do all the data entry per line on the “Jobs” sheet. The dates will not be in order within the “Jobs” sheet.

From the “Total Sheet”, I am trying to COUNT how many entries within the “Jobs” sheet fall within January (Date column) and contain an E (Status column).

The date column has dates in the following format:
01/05/06
The Status column has either an E or an S only.


I am also trying to do a SUM function based on these dependencies.

I would be grateful for any assistance given.
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

dave3009

Well-known Member
Joined
Jun 23, 2006
Messages
7,004
Office Version
  1. 365
  2. 2016
Hi

Is it possible for you to enter a new column in Jobs sheet

If so I have solved this exact problem with SUMIF.

In the new column you could put Jan next to a January date etc. In my case I also used data validation only to allow 3 letter month code. You then need to apply a name to the new column.

In the totals sheet next to January summary you have formula

=SUMIF(Name, "=Jan", "")

I think from memory

Perhaps someone with more experience will improve my solution


HTH


Dave
 

yee388

Well-known Member
Joined
Mar 7, 2004
Messages
1,374
Welcome to the board:
Book2
ABCDEF
1DateStatusNumberCount
216-Dec-2005E53option1
321-Dec-2005S103option2
426-Dec-2005E15
531-Dec-2005S20Sum
65-Jan-2006E25105option1
710-Jan-2006S30105option2
815-Jan-2006E35
920-Jan-2006S40
1025-Jan-2006E45
1130-Jan-2006S50
124-Feb-2006E55
139-Feb-2006S60
Sheet1


NOTE: option 2 is an array formula, and must be confirmed with CTRL+SHIFT+ENTER
 

gobias

New Member
Joined
Feb 23, 2006
Messages
18
You can use an array formula:

={SUM(IF(MONTH(Jobs!$A$3:$A$30)=1,IF(Jobs!$B$3:$B$30="A",1)))}

Change the range as needed. The 1 after "A" refers to month number, so change that to see another month.

It is an array formula, so you need to enter using ctrl+shift+enter. The enter button alone won't work.
 

gobias

New Member
Joined
Feb 23, 2006
Messages
18

ADVERTISEMENT

Sorry I meant the 1 after (Jobs!$A$3:$A$30) refers to the month.
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
My workbook consists of a “Jobs” sheet and a “Total Sheet” sheet. I do all the data entry per line on the “Jobs” sheet. The dates will not be in order within the “Jobs” sheet.

From the “Total Sheet”, I am trying to COUNT how many entries within the “Jobs” sheet fall within January (Date column) and contain an E (Status column).

The date column has dates in the following format:
01/05/06
The Status column has either an E or an S only.


I am also trying to do a SUM function based on these dependencies.

I would be grateful for any assistance given.

=SUMPRODUCT(--(DateRange-DAY(DateRange)+1="1-Jan-06"+0),--(StatusRange="E"))
 

Forum statistics

Threads
1,136,427
Messages
5,675,789
Members
419,586
Latest member
RoteichA

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top