Forum Discussion
What formula to use for multiplying countifs result?
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'.
If 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
9 Replies
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.
<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 resultCorrect?
