Forum Discussion
Formula help needed
Sample data only:
Table 1
Pls help what formula to use if data from table 1 be consolidate in the table 2 using formula
- how to generate the count of FSS/PSS/RSM/and so on who are under quarantine/RT-PCR+/and so on to table1 (data entry).
Table 2
Thank you.
Hi Malen0125,
I have questions: 1. para ma count nya ang entry sa table , necessary na in convert nya sa power query table? same like validation table.
A. Dili necessary pero it will make it mas sayon. I think the validation table is a static table, meaning it stays the same size dimension only to present values that are aggregated (summarized) related to the details of gathering information, typically most corporate businesses use a pivot table or data model. Since your data set isn't large enough to warrant an expansion of the validation table, you can have it as is, where it is and no need to change it until there's a need for expansion where you can decide on whether you want to used a pivot table that is based on a data model.
2. Is it possible na if naa ko entry in table (new case) automatice sya mo count didto sa table 2 (conso table).
A. Automatic=Dynamic. Para ma dynamic ang update sa table kinahanglan gamiton nmo ang excel formatted table kay pg mg add ka ug new entry mg expand mn ang table automatic, pati ang range sa formula automatic xa mg update.
13 Replies
Hi Malen0125 ,
Ang imo table ge convert nko using power query from this:
to this:
nya gamit ko ug ani na formula:
=IF(SUMIFS(_PQ1[Value],_PQ1[Designation],Table2!E$1,_PQ1[Attribute],Table2!$B3)=0,"",SUMIFS(_PQ1[Value],_PQ1[Designation],Table2!E$1,_PQ1[Attribute],Table2!$B3))
sa table 2
kung naa ka pangutana pm lang dire
file is attached
cheers
Hi, Yea_So
Wow! this is great. Thank you so much.
I have questions: 1. para ma count nya ang entry sa table , necessary na in convert nya sa power query table? same like validation table.
2. Is it possible na if naa ko entry in table (new case) automatice sya mo count didto sa table 2 (conso table).
Hi Malen0125,
I have questions: 1. para ma count nya ang entry sa table , necessary na in convert nya sa power query table? same like validation table.
A. Dili necessary pero it will make it mas sayon. I think the validation table is a static table, meaning it stays the same size dimension only to present values that are aggregated (summarized) related to the details of gathering information, typically most corporate businesses use a pivot table or data model. Since your data set isn't large enough to warrant an expansion of the validation table, you can have it as is, where it is and no need to change it until there's a need for expansion where you can decide on whether you want to used a pivot table that is based on a data model.
2. Is it possible na if naa ko entry in table (new case) automatice sya mo count didto sa table 2 (conso table).
A. Automatic=Dynamic. Para ma dynamic ang update sa table kinahanglan gamiton nmo ang excel formatted table kay pg mg add ka ug new entry mg expand mn ang table automatic, pati ang range sa formula automatic xa mg update.
- use sumproduct function for this,
if you could share sample file in excel, i can help out.Hi, Ravitosh Kumar
Yes, i can share it with you. Pls see attached document. hope to hear from you soonest.
Malen0125 i have converted data entry sheet to table. worked file attached.
=SUMPRODUCT((Table1[Designation]=E$5)*(Table1[[#Headers],[Anti-Body + / Antigen +]:[w/ FTW]]=$C10)*Table1[[Anti-Body + / Antigen +]:[w/ FTW]])summary data shows & works fine with additional data i tested.
it is intersection of all the 3 conditions: (designation) with (type of test) with (data values).
It looks like job for Power Query if transform entire table. If one by one perhaps SUMIFS() works. But it's hard to be more concrete without sample file and knowing on which version of Excel you are.
