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

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)
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
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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"))
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,255
Members
448,556
Latest member
peterhess2002

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
Back
Top