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

#### TexasEdge

##### New Member
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

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
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

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

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.

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

Thanks for all the input. I'll try these suggestions later.

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

Replies
1
Views
109
Replies
2
Views
221
Replies
8
Views
897
Replies
1
Views
260
Replies
3
Views
375

1,219,580
Messages
6,149,115
Members
450,861
Latest member
metcala

### 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.

### Which adblocker are you using?

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

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