Help with COUNTIFS and/or SUMPRODUCT

I've got a daily dataset that is non-continuous...it's a count of severe weather reports. So there can be gaps of several days with no reports. However, I need to get a count of report types by day and be able to fill a continuous timeline, including with zeros. Can anybody tell me what formula I can use to obtain, for example, a count of instances of "Thunderstorm Wind" in Column A for each date and have that populate Column F? I've experimented with both COUNTIFS and SUMPRODUCT but haven't had any luck? I need a formula in Column F that says:

1) The dates in Columns B & E match

AND

2) Column A = "Thunderstorm Wind"

Dates Table.PNG

Try

=SUMPRODUCT(--(A2:A33="Thunderstorm Wind"),--(B2:B33=E2:E33))

I tried that with a slight modification...Columns A & B extend down to Row 203 and Column E extends to Row 6562...

=SUMPRODUCT(--(A2:A203="Thunderstorm Wind"),--(B2:B203=E2:E6562))

And I get a "Value Not Available" error...

OK, Misunderstood...

your description sounded like you wanted to
Count row 2 if A2 = Thunderstorm wind, AND B2 = E2
Count row 3 if A3 = Thunderstorm wind, AND B3 = E3
etc.


But it appears you want
Count row 2 if A2 = Thunderstorm wind, AND B2 exists anywhere in E2:E6562
Count row 3 if A3 = Thunderstorm wind, AND B3 exists anywhere in E2:E6562

Is that right?

Yes...I think you've got what I'm looking for. Sorry for the confusion.

As an example from my table above, there are 2 instances of "Thunderstorm Wind" on 1/18/1979 (cells A8 and A9). I'm looking for a formula that can read that and place a "2" in cell F19. Make sense?

=countifs(A:A,"Thunderstorm Wind",B:B,E2)

Try

=SUMPRODUCT(--(A2:A203="Thunderstorm Wind"),--ISNUMBER(MATCH(B2:B203,E2:E6562,0)))

Originally Posted by Jonmo1

Try

=SUMPRODUCT(--(A2:A203="Thunderstorm Wind"),--ISNUMBER(MATCH(B2:B203,E2:E6562,0)))

Seems to just return a list of zeros.

Originally Posted by davsth

=countifs(A:A,"Thunderstorm Wind",B:B,E2)

Is this saying 'countif' anything in Column A = "Thunderstorm Wind" and anything in Column B = E2?

According to the picture you posted (an actual excel file attachment would be more helpful)
Judging by the left/right alignment of values in B and E, It looks like the dates in column B are not really dates, just Text strings that look like dates.
But Column E is real dates.

Test with these
=ISNUMBER(B2)
=ISNUMBER(E2)
Are they TRUE or FALSE ?

Originally Posted by Jonmo1

According to the picture you posted (an actual excel file attachment would be more helpful)
Judging by the left/right alignment of values in B and E, It looks like the dates in column B are not really dates, just Text strings that look like dates.
But Column E is real dates.

Test with these
=ISNUMBER(B2)
=ISNUMBER(E2)
Are they TRUE or FALSE ?

B2 = FALSE
E2 = TRUE

Excel file at link below...working off of Sheet 3:

https://nofile.io/f/gCfCPDygBjr/1979...+Filtered.xlsx

Originally Posted by bayouwxman

B2 = FALSE
E2 = TRUE

Excel file at link below...working off of Sheet 3:

https://nofile.io/f/gCfCPDygBjr/1979...+Filtered.xlsx

That file hosting site is taking a while...try this link instead:

http://bit.ly/2ntHQIJ

Yep, dates in B are not real dates. They'll need to be converted.
Try copying an empty cell
highlighting B
Right Click - Paste Special - Values - Add.

Originally Posted by bayouwxman

B2 = FALSE
E2 = TRUE

Excel file at link below...working off of Sheet 3:

https://nofile.io/f/gCfCPDygBjr/1979...+Filtered.xlsx

I guess the B2 date is text. try add in a Value.

=SUMPRODUCT(--(A2:A203="Thunderstorm Wind"),--ISNUMBER(MATCH(VALUE(B2:B203),E2:E6562,0)))

Originally Posted by Jonmo1

Yep, dates in B are not real dates. They'll need to be converted.
Try copying an empty cell
highlighting B
Right Click - Paste Special - Values - Add.

So copy Column B and then right click - paste special - values? I'm not seeing an "add" anywhere...probably a stupid question.

I do not have 2016 but under paste special you should have a section called operation you can select add and likewise values in the paste section

http://www.dummies.com/software/micr...in-excel-2016/

Originally Posted by finalazy

I guess the B2 date is text. try add in a Value.

=SUMPRODUCT(--(A2:A203="Thunderstorm Wind"),--ISNUMBER(MATCH(VALUE(B2:B203),E2:E6562,0)))

That returns a count of the total instances of "Thunderstorm Wind" in Column A but I need a count tied to each specific date.

No, copy an empty cell

Then highlight column B
Right Click -Paste Special (you have to actually click where it says paste special, don't just hover your mouse over it)
Select Values and Add, Click OK

Originally Posted by Jonmo1

No, copy an empty cell

Then highlight column B
Right Click -Paste Special (you have to actually click where it says paste special, don't just hover your mouse over it)
Select Values and Add, Click OK

Sorry...didn't quite understand the copy an empty cell at first. That returned a list of numbers beginning with 28856:

Web.png

oh =\ i guess that didn't work. Try converting this way. select the column B, go data tab and choose text to columns, click on next until you reach step 3 of 3, where you will see 4 option, select Date and press finish. See if that helps

I've got the dates for Column B in a separate sheet but they also include the time. I don't need the time so I had removed it before moving the dates into this sheet...

Web2.png

This is why posting an attachment is so much better than screenshots

all the dates should be dates, it makes life so much simpler however

=SUMPRODUCT(($A$1:$A$203="Thunderstorm Wind")*(TEXT($B$1:$B$203,"M/d/YYYY")=E1))

might give the answer you want

Originally Posted by finalazy

oh =\ i guess that didn't work. Try converting this way. select the column B, go data tab and choose text to columns, click on next until you reach step 3 of 3, where you will see 4 option, select Date and press finish. See if that helps

It looks like that worked...thanks!

Now...I've lost track...which formula should I try at this point?

Based on you agreeing with my understanding as described in post # 4
=SUMPRODUCT(--(A2:A203="Thunderstorm Wind"),--ISNUMBER(MATCH(B2:B203,E2:E6562,0)))

Now with new information that B includes the TIME with the date, but E does not...
=SUMPRODUCT(--(A2:A203="Thunderstorm Wind"),--ISNUMBER(MATCH(INT(B2:B203),E2:E6562,0)))

Originally Posted by Jonmo1

Based on you agreeing with my understanding as described in post # 4
=SUMPRODUCT(--(A2:A203="Thunderstorm Wind"),--ISNUMBER(MATCH(B2:B203,E2:E6562,0)))

Now with new information that B includes the TIME with the date, but E does not...
=SUMPRODUCT(--(A2:A203="Thunderstorm Wind"),--ISNUMBER(MATCH(INT(B2:B203),E2:E6562,0)))

So I just retried this from @davsth and it appears to work:

=countifs(A:A,"Thunderstorm Wind",B:B,E2)


Thanks to everyone for the help...really appreciate it!

Originally Posted by bayouwxman

So I just retried this from @davsth and it appears to work:

=countifs(A:A,"Thunderstorm Wind",B:B,E2)

Are you sure?
That isn't doing what we talked about in post # 4 and 5.

It will do this instead..
Count Row 2 if A2 = "Thunderstorm Wind" AND B2 = E2
Count Row 3 if A3 = "Thunderstorm Wind" AND B3 = E2
Count Row 4 if A4 = "Thunderstorm Wind" AND B4 = E2
etc.

I guess I really misunderstood your requirement.

Originally Posted by Jonmo1

Are you sure?
That isn't doing what we talked about in post # 4 and 5.

It will do this instead..
Count Row 2 if A2 = "Thunderstorm Wind" AND B2 = E2
Count Row 3 if A3 = "Thunderstorm Wind" AND B3 = E2
Count Row 4 if A4 = "Thunderstorm Wind" AND B4 = E2
etc.

I guess I really misunderstood your requirement.

Yea Jon, i thought so as well. I think initially you already provide the formula he wanted just that the B column date is in text which perhaps causes the problem.

Originally Posted by Jonmo1

Are you sure?
That isn't doing what we talked about in post # 4 and 5.

It will do this instead..
Count Row 2 if A2 = "Thunderstorm Wind" AND B2 = E2
Count Row 3 if A3 = "Thunderstorm Wind" AND B3 = E2
Count Row 4 if A4 = "Thunderstorm Wind" AND B4 = E2
etc.

I guess I really misunderstood your requirement.

I apologize if I wasn't clear but that formula is accomplishing exactly what I needed as seen in the screenshot below...

Web3.PNG