NCEP: Excel geniuses...

[2curlydogs]

So I have something like this

Table1:
Column A = ResponseDate
Column B = ID
Column C = Name
Column D = Email Me (possible values 1, 0, NULL, I'm looking for '1')

Sheet 2:
Column A = Date (2/19, 2/20, 2/21, etc.)
Column B = Total Registrants in a date range (using =COUNTIFS(Table1[ResponseDate],"="&A2))
Column C = Email Me Total

I need to know how many people selected "email me" on a specific date.

Halp?

ETA: I can get the total from the entire Table1 sheet with =SUMIF(Table1,"=1") but now I need to add the date filter

[josieposy]

Pivot table maybe? I've never done one from multiple sheets though.

[doxies]

Don't you just want to use SumIf the same way you used CountIfs (since the 1s would be summed)? So put in the Table1 response date criteria.


=SUMIF(Sheet2!$A$1:$A$1001,"="&A1,Sheet2!$D$1:$D$999)

[rosiedozie]

Mar 5, 2015 11:30:41 GMT -5 josieposy said:

Pivot table maybe? I've never done one from multiple sheets though.

Yep, I would do a pivot table off of the tab 1 data.

[sushi]

Mar 5, 2015 11:30:41 GMT -5 josieposy said:

Pivot table maybe? I've never done one from multiple sheets though.

It does work, although it can be slow. Iirc you can even pivot off another file

[andrealynn]

Does the "email me" in column D correspond with the response date in column A? If so, I would just filter column A for the date you are looking for and then do a countif on column D.

[wawa]

can't you just use countifs? That way you can specify multiple criteria. So it'd be =COUNTIFS(Table1[ResponseDate],"="&A2, Table1[Emailme],1) for each date.

[2curlydogs]

Mar 5, 2015 15:11:50 GMT -5 wawa said:

can't you just use countifs? That way you can specify multiple criteria. So it'd be =COUNTIFS(Table1[ResponseDate],"="&A2, Table1[Emailme],1) for each date.

This is what ended up working.

I swear the first time I tried this it was like #VALUE. And then I threw my computer across the room.

[andrealynn]

Mar 5, 2015 15:35:06 GMT -5 2curlydogs said:

Mar 5, 2015 15:11:50 GMT -5 wawa said:

can't you just use countifs? That way you can specify multiple criteria. So it'd be =COUNTIFS(Table1[ResponseDate],"="&A2, Table1[Emailme],1) for each date.

This is what ended up working.

I swear the first time I tried this it was like #VALUE. And then I threw my computer across the room.

Countifs is a fickle, fickle bitch.

[wawa]

Mar 5, 2015 15:35:06 GMT -5 2curlydogs said:

Mar 5, 2015 15:11:50 GMT -5 wawa said:

can't you just use countifs? That way you can specify multiple criteria. So it'd be =COUNTIFS(Table1[ResponseDate],"="&A2, Table1[Emailme],1) for each date.

This is what ended up working.

I swear the first time I tried this it was like #VALUE. And then I threw my computer across the room.

could have been a range issue.  the ranges have to be the exact same size for each criteria.  I have no idea WHY this is the case, but it is.