SUMPRODUCT based on text filter

Question

I am trying to get the sum of entries (numbers) in one column to return a result based on text in a different column:

If any cell in column B has the text "PA28-181", then I would like column G to add up all associated entires (flight times) that match that entry; ie I get to see how much time has been flown in a PA28-181. I can't see why the following wouldn't work, but it keeps returning a result of zero (I tried a different text that has no hyphen in it, but same result - 0 )!

=SUMPRODUCT(--ISTEXT(LOGBOOK!B7:B10050="PA28-181"),LOGBOOK!G7:G10050)

Then I would like to filter that further by just the last 6 months worth. Haven't gotten too far into that yet, but haven't find any applicable formulae online to date.

Answer 1

This is not tested, but it would something like this:

=SUMPRODUCT(--(LOGBOOK!B7:B10050="PA28-181")*(LOGBOOK!G7:G10050)*(LOGBOOK!A7:A10050+90>=TODAY()))

Here the 90 is 90 days in the past.

Be sure you test it, I did not

Answer 2

Try removing the call to ISTEXT():

=SUMPRODUCT(--(LOGBOOK!B7:B10050="PA28-181")*(LOGBOOK!G7:G10050))

Answer 3

Yes, in column A. Formatted as dd/mmm/yyyy. I was trying to use a different cell with today - 180 days and then using greater than, but no joy. The final calculation/filter would be to count back six months from the current day (today)

LOGBOOK!A6 contains TODAY()

Answer 4

Yes.....you need a column of dates.

Do you have a column of dates??

Answer 5

Yup that worked. Bizarre why the call would be the problem. Any thoughts on further filtering the result down to just the last six months?