Apr 23 2020 02:06 AM
Here is my current formula of C6. I need a new formula for C6 and my goal here is to
- find out how many time 'AED' in cell B6 appears (in spreadsheet 'query result') within the selected dates of cell B2 and B3
- also after finding out how many times AED appears, I need to multiply the count by the number appear in E column called NUM_OF_TXN (in spreadsheet 'query result').
Attachment 1: where new formula in C6 needs to be
Attachment 2: source the formula refers to spreadsheet called, 'query result'.
Apr 23 2020 04:19 AM
I guess the first part with COUNTIFS works. To find the number form column E you may use INDEX/MATCH assuming first found number for given date range will be taken (if it could be few of them). Formula could be like
=IFNA(INDEX(
<Target Column E>,
MATCH(1, INDEX(
(<Dates Column> >= <Start Date>)*
(<Dates Column> <= <End Date>)*
(<Currency Column> = <Currency Code>),0),
0)),0)
We return zero if nothing was found.
Apr 23 2020 08:29 PM - edited Apr 23 2020 08:35 PM
<Target Column E> means NUM_OF_TXN in column E of spreadsheet query result
<Currency Column> means TXN Currency in column B
<Currency Code> means TXN_CUR in column D of spreadsheet query result
Correct?
Apr 24 2020 12:58 AM
Apr 24 2020 02:37 AM - edited Apr 24 2020 02:43 AM
Thanks.
I tried the formula below both in excel and google sheets, but it still doesn't work.
The error says, 'Formula parse error'.
=IFNA(INDEX(
<'query result'!$E$2:$E$30000>,
MATCH(1, INDEX(
(<'query result'!$A$2:$A$30000> >= <$B$2>)*
(<'query result'!$A$2:$A$30000> <= <$B$3>)*
(<'query result'!$D$2:$D$30000> = <B6>),0),
0)),0)
I think it would be easier if I share the file with you.
Can I have your gmail address please? The file is in Google Sheets.
Apr 24 2020 06:36 AM
I emulated that in the attached file
In your formulas just remove placeholders signs < >.
Apr 27 2020 03:01 AM
I tried removing the <> and it works, however the result isn't what I expect to see.
In your query result spreadsheet, there're two AED results shown in D4 and D9. This means that the sum of AED appearing in between start date 19/10/2019 and end date 20/03/2020 should be (8+3) = 11.
However this formula shows the result as 3 instead of 11.
What formula should I use in this case?
Thank you,
Fern
Apr 27 2020 03:15 AM
SolutionIf to sum that will be another task, formula for it is
=SUMIFS(
'query result'!E:E,
'query result'!A:A,">="&$B$2,
'query result'!A:A,"<="&$B$3,
'query result'!D:D,B6
)
as here
Apr 27 2020 09:28 PM
Apr 27 2020 11:42 PM
@little2fern , you are welcome, glad to help
Apr 27 2020 03:15 AM
SolutionIf to sum that will be another task, formula for it is
=SUMIFS(
'query result'!E:E,
'query result'!A:A,">="&$B$2,
'query result'!A:A,"<="&$B$3,
'query result'!D:D,B6
)
as here